In psql, \l lists databases, \c dbname connects to one, and \dt lists the tables in the current database. From SQL or any other client, query the catalogs: pg_database for databases and information_schema.tables for tables.
It returns the two tables as BASE TABLE and the view as VIEW, all in the public schema. The WHERE filter hides the system catalogs, which would otherwise add hundreds of rows. Add AND table_type = 'BASE TABLE' to leave out views.
psql commands for databases and tables
These are psql meta-commands. psql turns each one into a catalog query, so they do not work in other clients or in application code.
| Command | Shows |
|---|---|
\l | Databases (\l+ adds sizes) |
\c shop | Connects to the database shop |
\conninfo | The database, user and host you are on |
\dn | Schemas |
\dt | Tables in the schemas on your search_path |
\dt sales.* | Tables in the schema sales |
\dt *.* | Tables in every schema, system ones included |
\dt+ | Tables with size and description |
\dv, \di, \ds | Views, indexes, sequences |
\d orders | Columns, indexes and constraints of orders |
\d+ orders | The same plus storage and comments |
\du | Roles |
From the shell, psql -l prints the database list and exits, and -c runs one command:
psql -l
psql -d shop -c '\dt'
Start psql with -E to print the SQL behind each meta-command. It is a quick way to learn the catalog tables.
List databases with SQL
Every database on the server is a row in pg_database. template0 and template1 are the templates CREATE DATABASE copies, so filter them out:
On this page each run gets a fresh database whose name starts with lesson_. The result shows it and postgres, the default database every server creates. SELECT current_database() returns the one you are connected to.
Describe a table's columns
In psql, \d orders describes the table. In SQL, information_schema.columns holds one row per column:
id shows NO under is_nullable because a primary key is always NOT NULL. Its column_default is empty: an identity column takes its values from a sequence, not from a default.
Query pg_catalog directly
information_schema is the SQL standard's view and shows only objects you have some privilege on. The PostgreSQL-specific catalogs in pg_catalog hold more, such as index definitions:
It lists three indexes: orders_email_key from UNIQUE, orders_pkey from the primary key and the partial one you created. Each comes with the CREATE INDEX statement that would rebuild it. pg_tables lists tables only; pg_views and pg_matviews cover views and materialized views.
Count the rows in every table
The catalogs do not store exact row counts. pg_class.reltuples holds the planner's estimate, which VACUUM and ANALYZE update. It is close enough to spot the large tables, and -1 means the table was never analyzed:
SELECT relname, reltuples::bigint AS estimated_rows
FROM pg_class
WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace
ORDER BY reltuples DESC;
For an exact number, run SELECT count(*) FROM orders, which reads the whole table. The catalog tables are documented under System Catalogs in the PostgreSQL manual.
Frequently Asked Questions
Does PostgreSQL have SHOW TABLES or SHOW DATABASES?
No, those are MySQL commands. In PostgreSQL, SHOW reads a server setting, so SHOW TABLES fails with "unrecognized configuration parameter". Use \dt and \l in psql, or query information_schema.tables and pg_database.
Why does \dt say "Did not find any relations"?
Either you are connected to a different database than the one holding the tables, or the tables are in a schema that is not on your search_path. Check \conninfo, then try \dt *.* or \dt schema_name.*.
How do I switch databases in PostgreSQL?
A connection belongs to one database, and SQL has no USE statement. In psql run \c other_db, which opens a new connection; from the shell, start psql -d other_db.
How do I see table sizes in PostgreSQL?
In psql, \dt+ adds a size column. In SQL, pg_size_pretty(pg_total_relation_size('orders')) gives the size of a table including its indexes and TOAST data.