Show Tables in PostgreSQL: psql, SQL Queries, and Schema Filters

You type SHOW TABLES; into psql out of MySQL habit, and PostgreSQL rejects the statement: here SHOW reports run-time parameters, not tables. Inside psql the table list comes from a meta-command:

\dt

From any other client it comes from a query:

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

Everything below is for PostgreSQL 18, on this schema:

CREATE TABLE customers (customer_id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE orders (order_id int PRIMARY KEY, customer_id int NOT NULL REFERENCES customers);
CREATE TABLE products (product_id int PRIMARY KEY, name text NOT NULL);

CREATE SCHEMA sales;
CREATE TABLE sales.invoices (invoice_id int PRIMARY KEY, order_id int NOT NULL);

What show tables means in PostgreSQL

The request usually turns out to be one of five:

• list the user tables in the current schema
• list the tables in one named schema
• list the tables across every schema
• add the size or the owner of each table
• inspect the structure of one table once you have found it

PostgreSQL 18 answers none of them with a SHOW statement. Its SHOW command takes a parameter name or ALL, and it "will display the current setting of run-time parameters", so the table list comes from psql or from the catalog.

Which method works in which client

psql interprets \dt, \dt+ and \d itself and never sends them to the server, so they work in a psql session and nowhere else. In a JDBC client, in an application, and in the DbSchema SQL Editor, you query the catalog instead.

Two catalog views answer the question, and they answer it slightly differently. information_schema.tables is the standard SQL view, and it "contains all tables and views defined in the current database". pg_catalog.pg_tables is PostgreSQL's own view, with schemaname, tablename, tableowner, tablespace and four flags: hasindexes, hasrules, hastriggers and rowsecurity.

Show tables in psql

Connect to a database first

psql -U postgres -d database_name

Inside psql, \l lists the databases on the server and \c switches to one of them:

\l
\c database_name

How to Create a Database in PostgreSQL and Essential PostgreSQL Commands cover the rest of the session basics.

List the tables in the search path

\dt

The invoices table is missing because \dt without a pattern lists only what the search path reaches. A table in another schema needs a pattern.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

List the tables in one schema

\dt sales.*

A name pattern works the same way, so \dt public.order* returns the orders row on its own.

List the tables in every schema

\dt *.*

A pattern also switches on the system objects that \dt hides by default. The psql documentation puts it as "supply a pattern or the S modifier to include system objects", so the listing holds the catalog tables of pg_catalog and information_schema alongside your four. The S modifier on its own, \dtS, is the same switch without the schema pattern. Reach for it when you are comparing your tables against catalog objects.

Add sizes to the listing

\dt+

The + variant lists each object with "its persistence status (permanent, temporary, or unlogged), physical size on disk, and associated description if any". It prints the access method as well, and has done since PostgreSQL 14, whose release notes record "Add an access method column to psql's \d[i|m|t]+ output". The bracket notation there is psql's own shorthand for \di+, \dm+ and \dt+. The size column answers which table is the biggest without writing any SQL.

Show tables with SQL queries

Use information_schema

The standard view is the portable choice, and excluding the two catalog schemas leaves the tables you created:

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

The table_type filter matters: the same view holds views as well, under the type VIEW, plus FOREIGN for foreign tables and LOCAL TEMPORARY for temporary ones.

List the current schema only

SELECT table_name
FROM information_schema.tables
WHERE table_schema = current_schema()
  AND table_type = 'BASE TABLE'
ORDER BY table_name;

Use pg_catalog for PostgreSQL-specific columns

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

Add the size of each table

SELECT n.nspname AS table_schema,
       c.relname AS table_name,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n
  ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC;

pg_class holds every relation, and relkind says which kind each one is: r for an ordinary table and p for a partitioned table, next to i for an index, v for a view and m for a materialized view. Filtering on both r and p keeps the partitioned tables in the list, which is what you want before archiving data or adding an index.

Show related objects and schemas

The letters after \d name the kind of object, so the table commands have siblings:

The letters also combine. \dti lists tables and indexes together, which saves a second command when you are working out what an unfamiliar name refers to.

For schema-level tutorials, see List All Schemas in PostgreSQL and Create Table in PostgreSQL.

Describe a specific table

A table list tells you a name exists. The next question is what is inside it, and \d with a table name answers it:

\d customers

The listing shows every column, its type, its tablespace when that is not the default, and attributes such as NOT NULL and defaults. Under the columns come the indexes, constraints, rules and triggers attached to the table. Adding the + gives the column comments, the view definition when the object is a view, a non-default replica identity and the access method:

\d+ customers

Describe Table in PostgreSQL goes through the output field by field.

Show tables visually in DbSchema

A table list is a flat list, and it says nothing about which table points at which. DbSchema draws that instead of printing it:

1. Click Connect to Database, pick PostgreSQL in Choose Your Database, and fill in the Connection Dialog. The PostgreSQL JDBC driver is downloaded for you.
2. Let DbSchema reverse-engineer the schema and lay every table out on an interactive diagram, with the foreign key lines drawn between them.
3. Double-click a table header to open the Table Dialog and read its columns, indexes and foreign keys.
4. Open the SQL Editor from the Editors menu when you want one of the catalog queries above instead.

Reverse-engineering reads the database and fills the model, and the diagram is a view of that model, so exploring an unfamiliar schema this way changes nothing in the database. Statements you run in the SQL Editor go to the database as written.

Once you have found your way around, How to Create a Table in PostgreSQL is the next step. Download DbSchema at https://dbschema.com/download.html, connect to the PostgreSQL database you were listing, and read the tables off the diagram. Connecting, reverse-engineering and the diagrams are in the free Community Edition.

FAQ

Why does \dt show no tables?

\dt without a pattern lists only the schemas the search path reaches, and in the default setup that path is "$user", public. Tables in any other schema need \dt schema_name.*, or \dt *.* to sweep everything. A table you hold no privilege on is missing from information_schema.tables for a different reason: the view shows only what "the current user has access to (by way of being the owner or having some privilege)".

How do I include table sizes in the result?

Use \dt+ in psql, or the pg_class query above from any client. The function it calls, pg_total_relation_size, "computes the total disk space used by the specified table, including all indexes and TOAST data", so swap in pg_table_size when you want the table without its indexes.