How to List Tables in PostgreSQL

Galaxy Glossary

How do I list tables in PostgreSQL?

Shows every user-visible table in the current database using psql or system catalogs.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

How do I list all tables in PostgreSQL?

Run \dt inside psql to display every table in the current schema. The command relies on pg_catalog.pg_class and filters out system objects by default.

How can I list tables in every schema?

Prefix the meta-command with a wildcard: \dt *.*. The pattern before the dot is the schema filter; the one after is the table filter. Use * to match everything.

Can I use plain SQL instead of psql commands?

Yes. Query information_schema.tables or pg_catalog.pg_tables when you need code that works outside psql.

SQL to list user tables only

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

How do I show tables in a specific schema?

Use \dt schema_name.* or add WHERE schemaname = 'schema_name' to the SQL query.

What is the exact syntax?

See the next section for a full syntax reference covering psql and SQL approaches.

Best practices for listing tables

1️⃣ Use \x (expanded mode) for wide output. 2️⃣ Combine \dt+ * with \pset format asciidoc for copy-pasting. 3️⃣ Prefer information_schema in portable scripts.

Common mistakes and how to fix them

Wrong database: Forgetting to \c database_name first shows an empty list. Connect to the intended database before running \dt.

Hidden system tables: Using SELECT * FROM pg_tables without a WHERE clause returns system tables, causing confusion. Filter out pg_catalog and information_schema.

Why How to List Tables in PostgreSQL is important

How to List Tables in PostgreSQL Example Usage


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

How to List Tables in PostgreSQL Syntax


psql meta-command:
  \dt [schema-pattern.]table-pattern [*+]   -- tables only
  \dS                                          -- include system tables
  \dt+                                         -- add size & description columns
  \dt *.*                                      -- every schema

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

Common Mistakes

Running \dt in the wrong database: You connect to the default database and see no rows. Fix by executing \c target_db before \dt or include the -d flag when launching psql.
Forgetting to exclude system schemas: Querying pg_tables without a WHERE clause floods output with system tables. Add WHERE schemaname NOT IN ('pg_catalog','information_schema') or use \dt instead of \dS.

Frequently Asked Questions (FAQs)

Is \dt available outside psql?

No. \dt is a psql client shortcut. Use SQL queries against information_schema or pg_catalog if you need programmatic access.

How do I list only materialized views?

Run \dm for materialized views or query pg_matviews.

Can I sort the table list?

Yes. Append ORDER BY schemaname, tablename in SQL or pipe psql output through UNIX sort.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie