PostgreSQL List Tables: Every Method, From \dt to the System Catalogs

You’re in a psql prompt, or halfway through a script, and you just want to see what tables exist in the database. Instinct says SHOW TABLES; — that’s what MySQL trained you to type. Postgres doesn’t have that statement, so you get a syntax error instead of a table list.

Here’s the fast answer, then the rest of the decision tree.

The one-line answer: \dt

If you’re inside psql, type:

\dt

This lists the tables in the schemas on your current search path — for most setups, that means the public schema. It’s a psql meta-command, not SQL, so it only works inside the interactive psql client (or a script fed to it), not as a query you can embed in application code.

Want more detail — size, description — without switching approaches? Add a plus sign:

\dt+

\dt+ shows the same table listing with extra columns, including size, alongside whatever detail psql adds for the plus-form of the command. That’s the version I reach for when I’m eyeballing a database I didn’t build, because seeing which tables are unexpectedly large is often more useful than just knowing they exist.

If \dt and \dt+ cover 90% of your day-to-day needs, the rest of this article is about the other 10%: specific databases, specific schemas, excluding system noise, scripting, permissions, and the objects \dt won’t show you.

Why there’s no SHOW TABLES in Postgres

This trips up almost everyone arriving from MySQL, and it’s worth understanding rather than just memorizing the workaround. PostgreSQL does not implement a SHOW TABLES statement the way MySQL does. There’s no equivalent single-keyword SQL command baked into the server for this — instead, you use either a client-side meta-command (\dt in psql) or a query against the system catalogs.

This isn’t a gap so much as a different design philosophy. MySQL’s SHOW commands are convenience syntax layered onto the SQL engine. Postgres instead exposes its metadata as regular queryable tables and views — pg_catalog and information_schema — and lets the client (psql) provide convenience shortcuts on top. Once that clicks, a lot of other “why doesn’t Postgres just have a command for X” questions answer themselves the same way: check \d, \l, \du, and friends, or query the catalog directly.

Connecting to the right database first

\dt only shows tables in whatever database you’re currently connected to — it doesn’t cross database boundaries. If you fired up psql against the wrong database, or you’re on a multi-database server and need a different one, connect first.

From inside psql:

\c my_database
\dt

\c (or \connect) switches your current connection to the named database, and then \dt lists tables in that database’s search path. If you’re not sure which databases exist, \l lists them before you connect.

From the shell, you can skip the two-step and connect directly to the right database with the -d flag:

psql -d my_database -c '\dt'

Connecting to a specific database — whether via \c inside psql or the -d flag when launching it — is the prerequisite step people skip when \dt “isn’t showing my tables”: you’re just looking at the wrong database. This is the single most common reason \dt appears to return nothing or the wrong list — not a permissions problem, not a bug, just a connection to a database that doesn’t contain the tables you expect.

\d vs \dt: don’t confuse structure with listing

Related but distinct: \d with no arguments and \dt are not the same command, and it’s easy to mix them up under pressure.

• \d (no table name) gives a quick listing of objects — tables, views, sequences, and so on — including name, schema, type, and owner.
• \dt narrows that same kind of listing specifically to tables.
• \d table_name — with an argument — flips into detail mode: columns, data types, constraints, indexes for that one table.

If you want a broad inventory of every kind of object, \d alone is often more informative than \dt. If you specifically want tables and nothing else, \dt is more precise. If you want to inspect one table’s structure, \d table_name is the right tool — that’s effectively Postgres’s answer to DESCRIBE TABLE.

The SQL-standard way: information_schema.tables

\dt is fast, but it’s psql-only — it won’t run inside a Python script, a Django migration check, or a BI tool’s SQL editor. For that, query information_schema.tables:

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

information_schema is a standardized metadata schema, and it’s also found in several other major SQL databases, including MySQL and SQL Server. Because the schema itself is standardized, a similar query pattern often carries over between engines with only minor syntax differences — though it’s not a guarantee, so worth double-checking against whichever engine you’re targeting. If you’re writing a script that might run against more than one database engine, or you just don’t want to relearn a Postgres-specific catalog, this is the more portable choice.

One gotcha: querying information_schema.tables without filtering returns views as well as tables, because both show up in the same view. The table_type column is what separates them, so filter on it when you want only real tables:

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

table_type = 'BASE TABLE' excludes standard views, leaving genuine tables.

Excluding system tables

Whether you go through information_schema or pg_catalog, an unfiltered query against the full catalog will show you a lot of internal Postgres plumbing you almost never care about.

Postgres organizes objects into schemas: public is the default home for tables you create, pg_catalog holds the system catalog tables, information_schema holds the SQL-standard metadata views, and pg_toast is used internally for storing oversized column values. When you’re hunting for your tables, you generally want to exclude the last three.

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
  AND table_type = 'BASE TABLE';

Filtering out pg_catalog and information_schema in a WHERE clause is the standard way to strip system noise and leave only user-created tables in the result. The same filter pattern applies whether you’re querying information_schema.tables or pg_catalog.pg_tables — the schema names being excluded don’t change.

The Postgres-native way: pg_catalog.pg_tables

If portability doesn’t matter and you want Postgres-specific metadata, pg_tables gives you more than information_schema.tables does:

SELECT schemaname, tablename, tableowner
FROM pg_catalog.pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');

pg_catalog.pg_tables is a Postgres-specific view, and unlike information_schema.tables it surfaces the table’s owner along with boolean flags like hasindexes, hasrules, and hastriggers. That’s genuinely useful when you’re auditing a database you inherited — “does this table have any triggers I don’t know about?” is a one-column answer instead of a separate catalog lookup.

Permission denied on pg_tables

Occasionally a lower-privileged role gets permission denied for table pg_tables when running this query directly. That error typically happens when a non-privileged user tries to query the underlying system catalog table rather than the view layer meant for general access, and the usual fix is to use the more permissively-accessible views — pg_catalog.pg_tables or information_schema.tables — instead of poking at raw catalog internals. If you’re scripting this as part of an automated health check, don’t assume every environment’s connecting role has the same privileges — check which view you’re actually allowed to read before the script runs in production.

What \dt and information_schema.tables both miss: materialized views

If your database uses materialized views for caching expensive query results, neither \dt nor information_schema.tables will show them to you by default. Materialized views don’t appear in information_schema.tables at all — you need a separate lookup: the pg_catalog.pg_matviews view, or the psql shortcut \dm.

\dm

or

SELECT schemaname, matviewname
FROM pg_catalog.pg_matviews;

This is an easy blind spot. I’ve seen “where did this data come from” confusion resolved entirely by remembering that the answer was a materialized view nobody thought to check for, because it simply wasn’t in the standard table listing.

Row counts: quick estimate vs. exact count

A natural follow-up once you can list tables is “how big is each one?” There are two very different ways to answer that, and conflating them causes real performance problems.

Catalog statistics — the kind Postgres maintains and refreshes via ANALYZE — give you a fast estimated row count without scanning the table. Running SELECT COUNT(*) against every table, by contrast, requires a full scan per table and can be slow on large databases, especially if you’re looping over dozens of tables in a script.

Method	Speed	Accuracy	Use case
Catalog statistics (e.g., via pg_stat_user_tables, updated by ANALYZE)	Fast	Estimate	Quick sizing checks, dashboards, scripts run often
SELECT COUNT(*) per table	Slow on large tables	Exact	One-off audits where precision matters

If you’re building a monitoring dashboard that lists tables alongside row counts, use the estimate — it’s the difference between a query that returns instantly and one that locks up scanning terabytes for a number nobody’s going to act on precisely. Save COUNT(*) for the specific table you actually need an exact figure from.

Choosing between methods

With four-ish tools on the table, here’s how I’d decide, fast:

• Just looking, in psql, right now: \dt or \dt+ if you want size info too.
• Writing a script or app code that needs to run the same way regardless of database engine: information_schema.tables, filtered on table_type and schema.
• Writing a script that only ever targets Postgres, and you want owner/index/trigger flags: pg_catalog.pg_tables.
• Auditing a database you didn’t build, and want a full inventory including structure: start with \d, drill into individual tables with \d table_name.
• Need materialized views too: don’t forget \dm or pg_matviews — none of the above include them.

None of these is objectively “correct.” The interactive/scripted split matters more than the portable/native split for most day-to-day work — reach for \dt when a human is reading the output, reach for a catalog query when a program is.

Conclusion

There’s no SHOW TABLES in Postgres, and once you stop looking for it, the actual options are more flexible than the single MySQL command anyway. \dt covers the interactive case in one keystroke; \dt+ adds size for free; information_schema.tables gives you portability and a table_type filter for excluding views; pg_catalog.pg_tables gives you Postgres-native detail like ownership and trigger flags. The gotchas worth remembering are the ones that cost the most debugging time: connect to the right database before you panic about missing tables, filter out pg_catalog and information_schema when you want only your own tables, and check \dm separately if materialized views are part of your schema. Pick the tool that matches whether a human or a script is reading the result, and the rest is copy-paste.

FAQ

Why doesn’t SHOW TABLES; work in PostgreSQL? Because Postgres doesn’t implement that statement — it’s a MySQL-specific command with no direct Postgres equivalent. Use \dt in psql or a query against information_schema.tables / pg_catalog.pg_tables instead.

How do I list tables in a database other than the one I’m connected to? Connect to it first — either with \c database_name from inside psql, or by launching psql with psql -d database_name — then run \dt.

Does \dt show views too? No — \dt is scoped to tables specifically; use \dv for views or \d with no argument for a mixed listing of all object types.

Why do I see views when I query information_schema.tables? Because that view includes both tables and views by default; filter with WHERE table_type = 'BASE TABLE' to get tables only.

How do I list only my own tables, not Postgres’s internal ones? Exclude the pg_catalog and information_schema schemas in your WHERE clause, whether you’re querying information_schema.tables or pg_catalog.pg_tables.

Why did I get “permission denied for table pg_tables”? That usually means your role is trying to query a restricted system catalog table directly rather than the more accessible view; switch to pg_catalog.pg_tables or information_schema.tables.

Where are my materialized views — why aren’t they in \dt or information_schema.tables? Materialized views are a separate object type and don’t show up in either; use \dm in psql or query pg_catalog.pg_matviews.