DEV Community

Cover image for PostgreSQL: How to Show Tables Using PSQL or SQL Queries
DbVisualizer
DbVisualizer

Posted on

PostgreSQL: How to Show Tables Using PSQL or SQL Queries

#postgressql

If you come from MySQL, you might instinctively type SHOW TABLES; in PostgreSQL — and get an error. PostgreSQL doesn’t include that command, but there are easy alternatives.

In this post, we’ll go through both ways to list tables: using psql’s built-in commands and SQL queries from the system catalogs. You’ll also see a few extra tricks to refine your results.

Method 1: Using the PSQL Shell

Connect to a database from your terminal:

\c postgres
Enter fullscreen mode Exit fullscreen mode

Then list all tables in the current schema:

\dt
Enter fullscreen mode Exit fullscreen mode

Want to see all tables across schemas?

\dt *.*
Enter fullscreen mode Exit fullscreen mode

You can also view details about a specific table:

\d table_name
Enter fullscreen mode Exit fullscreen mode

Extra Tip: To display only tables owned by a user:

\dt *.* | grep alice
Enter fullscreen mode Exit fullscreen mode

Method 2: Using SQL Queries

Use a query on PostgreSQL’s catalog or information_schema:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_type = 'BASE TABLE';
Enter fullscreen mode Exit fullscreen mode

Or show all tables grouped by schema:

SELECT table_schema, table_name
FROM information_schema.tables
ORDER BY table_schema, table_name;
Enter fullscreen mode Exit fullscreen mode

You can even use the internal pg_catalog.pg_tables view:

SELECT schemaname, tablename, tableowner
FROM pg_catalog.pg_tables
ORDER BY schemaname;
Enter fullscreen mode Exit fullscreen mode

Bonus – Include Views:

SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public';
Enter fullscreen mode Exit fullscreen mode

Extra Example: Using a Database Client

If you prefer visual tools, clients like DbVisualizer let you view all tables directly in the left-hand schema explorer and run the same queries interactively.

-- Example: run in DbVisualizer SQL Commander
SELECT * FROM information_schema.tables WHERE table_schema = 'public';
Enter fullscreen mode Exit fullscreen mode

FAQ

Does PostgreSQL have SHOW TABLES?

No, it doesn’t. Use \dt or system catalog queries instead.

Can I get only tables from one schema?

Yes, add a WHERE table_schema = 'schema_name' condition.

Can I include views or foreign tables?

Remove AND table_type = 'BASE TABLE' to list all objects.

Can I see more info like owner and type?

Yes — use pg_catalog.pg_tables and include tableowner.

Conclusion

PostgreSQL doesn’t have a direct SHOW TABLES command, but the alternatives are just as effective. Between \dt in the shell, system catalog queries, and visual clients like DbVisualizer, you can quickly explore database structures and schemas.

To dive deeper with full queries and screenshots, check out the original article on showing tables in PostgreSQL on DbVisualizer’s TheTable blog.

Top comments (0)