PostgreSQL: How to Show Tables Using PSQL or SQL Queries

If you’re switching from MySQL to PostgreSQL, you might look for the SHOW TABLES command — only to discover it doesn’t exist here. Fortunately, PostgreSQL gives you equally convenient options for listing all tables.

You can use either the PSQL shell or a system catalog query. Both give you a clear overview of tables, schemas, and structures.

Let’s explore both methods with practical examples.

Method 1: List Tables in PSQL

In PostgreSQL’s interactive terminal psql, connect to your database:

\\c postgres

Then use:

\\dt

to list tables in the current schema. You can also show tables across all schemas with:

\\dt *.*

Pro tip: To see only tables owned by a user:

\\dt *.* | grep username

Method 2: Query the System Catalog

The SQL-based approach uses information_schema.tables:

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

This displays all “base” tables (not views or foreign tables) in the public schema.

Want a broader look? Try this version:

SELECT table_schema, table_name, table_type
FROM information_schema.tables
ORDER BY table_schema, table_name;

Extra Example – Include Views Too:

SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public';

This shows both base tables and views in your schema — useful for a complete overview.

Bonus Example: Use pg_catalog Directly

For advanced inspection, query PostgreSQL’s internal catalog:

SELECT schemaname, tablename
FROM pg_catalog.pg_tables
ORDER BY schemaname, tablename;

This gives you an engine-level look at tables across all schemas, not just user-defined ones.

FAQ

Is there a SHOW TABLES command in PostgreSQL?

No. Use \\dt in psql or information_schema.tables queries instead.

Can I filter tables by schema?

Yes. Add WHERE table_schema = 'schema_name' in your query.

Can I see views or foreign tables?

Yes — remove the AND table_type = 'BASE TABLE' condition.

Can I see who owns each table?

Add the table_owner column from pg_tables.

Conclusion

While PostgreSQL doesn’t have SHOW TABLES, you still have flexible ways to explore your schema — whether from the terminal or via SQL queries. Combining these with a database client like DbVisualizer makes navigating, filtering, and visualizing tables much faster.

To see the full tutorial and examples, visit the full article Show Tables PostgreSQL Guide: Two Different Approaches for more examples.