PostgreSQL gives you several ways to list tables, and the choice between them is not arbitrary. The three main paths - psql meta-commands, information_schema, and pg_catalog - exist for different reasons, expose different data, and carry different trade-offs worth understanding before you embed any of them in a script or application.
Using psql Meta-Commands
The fastest way to list tables when you're already in a psql session is \dt. It returns all tables in the current search path, across whatever schemas that path resolves to.
\dt
By default, this shows Schema, Name, Type, and Owner. Appending a + gives you extended output:
\dt+
The + variant adds Persistence (permanent, temporary, or unlogged), Access method (heap by default), Size, and Description. Size here comes from pg_table_size(), which includes the main fork, free space map, visibility map, and TOAST data — but excludes indexes. Use pg_total_relation_size() to include indexes. That distinction matters if you're eyeballing storage use and wondering why your table's footprint looks smaller than expected.
Pattern matching works with \dt. \dt public.* lists all tables in the public schema. \dt orders* lists any table whose name starts with orders within schemas visible in the current search_path; to match across all schemas, use \dt *.orders*. For views, use \dv. For materialized views, \dm. These meta-commands exist because they're quick to type interactively, but they don't compose well into automation - for that, you'll want SQL.
Querying information_schema.tables
The information_schema is a SQL standard construct. PostgreSQL implements it as a set of views over the underlying system catalogs. The intent is cross-database portability: the same query structure works against MySQL, SQL Server, or PostgreSQL.
SELECT table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;
table_type will be 'BASE TABLE' for ordinary tables and 'VIEW' for views. Filtering by table_schema = 'public' restricts the result to a single schema. This view is intentionally limited to what the SQL standard defines - so columns like owner, tablespace, or whether a table has indexes are absent.
One real limitation: materialized views do not appear in information_schema.tables at all. The SQL standard has no concept of a materialized view, and PostgreSQL deliberately doesn't extend the standard view in a way that would misrepresent the semantics. If your codebase relies on information_schema and you're introducing materialized views, you will miss them entirely unless you query pg_catalog separately.
Another thing to watch: information_schema views in PostgreSQL filter results by the current user's privileges. A table your role cannot access won't appear. That behavior is correct by spec but can mislead you during a schema audit if you're not connected as a superuser.
Querying pg_catalog for Full Detail
pg_catalog.pg_tables is a PostgreSQL-native view built directly from pg_class, pg_namespace, and pg_tablespace. It exposes more columns than information_schema and does not filter by privilege.
SELECT schemaname, tablename, tableowner, tablespace,
hasindexes, hasrules, hastriggers, rowsecurity
FROM pg_catalog.pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;
The boolean flags hasindexes, hasrules, hastriggers, and rowsecurity are genuinely useful. If you're writing a migration validator that checks whether row-level security is applied to sensitive tables, rowsecurity gives you that in a single column without joining to pg_class yourself.
For views, query pg_catalog.pg_views. For materialized views, use pg_catalog.pg_matviews, which also includes ispopulated (a boolean indicating whether the view has been refreshed) and definition (the SQL definition). There is no equivalent for ispopulated in information_schema - it doesn't exist there at all.
SELECT schemaname, matviewname, matviewowner, ispopulated
FROM pg_catalog.pg_matviews
WHERE schemaname = 'public';
The pg_catalog approach gives you OIDs if you need them. The pg_class.oid for a table is a stable identifier within a single PostgreSQL instance (though not across instances), and tools like pg_dump rely on OIDs internally. If you're writing low-level tooling, starting from pg_tables and joining back to pg_class via relname and relnamespace is a common pattern.
Schema Filtering and Multi-Schema Databases
PostgreSQL databases often contain multiple schemas: public, application-level schemas, and potentially schemas owned by extensions (like postgis or timescaledb). The pg_catalog and information_schema schemas are always present and rarely what you want when listing "your" tables.
A practical filter for excluding system schemas:
WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
If you're running PostGIS, you may also want to exclude the topology schema. Extensions can create their own schemas, so the right exclusion list depends on what's installed.
To scope a query to a specific schema, filter on table_schema (in information_schema) or schemaname (in pg_catalog). Both columns hold the schema name as text. There's no functional difference between the two for this purpose - it's purely which view you're querying.
Practical Use Cases: Schema Discovery in Automation
Schema discovery is a common requirement in migration tools, ORMs, data catalog systems, and health check scripts. The right query depends on what you actually need.
For a portable existence check - say, verifying a table exists before running a migration - information_schema.tables is fine:
SELECT COUNT(*)
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name = 'users';
For a full schema audit that includes ownership, triggers, and RLS status, use pg_catalog.pg_tables joined with pg_catalog.pg_class if you need the OID:
SELECT t.schemaname, t.tablename, t.tableowner,
t.hasindexes, t.hastriggers, t.rowsecurity,
c.oid
FROM pg_catalog.pg_tables t
JOIN pg_catalog.pg_class c
ON c.relname = t.tablename
JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace
AND n.nspname = t.schemaname
WHERE t.schemaname NOT IN ('pg_catalog', 'information_schema');
For tools that need to enumerate all queryable objects - tables, views, materialized views, partitioned tables, and foreign tables together - there's no single view that covers all of them. You'll need to UNION results from pg_tables, pg_views, and pg_matviews, or work directly from pg_class filtering on relkind ('r' for tables, 'v' for views, 'm' for materialized views, 'p' for partitioned tables (PostgreSQL 10+), 'f' for foreign tables).
SELECT nspname AS schema, relname AS name,
CASE relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'm' THEN 'materialized view'
WHEN 'p' THEN 'partitioned table'
WHEN 'f' THEN 'foreign table'
END AS type
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE relkind IN ('r', 'v', 'm', 'p', 'f')
AND nspname NOT IN ('pg_catalog', 'information_schema');
This pattern avoids the privilege filtering of information_schema and avoids running three separate queries. It's the foundation most database introspection tools build on when targeting PostgreSQL specifically.
The short summary: use \dt interactively, information_schema when portability across database engines matters, and pg_catalog when you need complete, PostgreSQL-native metadata - especially for materialized views, RLS flags, OIDs, or any attribute the SQL standard doesn't define.
