List tables, databases, schemas and users in PostgreSQL

In psql:

\dt              -- tables in the schemas on your search_path
\dt *.*          -- tables in every schema
\l               -- databases
\dn              -- schemas
\du              -- roles (users)
\d users         -- describe one table
\c other_db      -- switch database

From SQL, which works from any client or application:

-- tables
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;

\dt is not SQL. It is a feature of the psql client, which expands it into a real query against the system catalogues before anything reaches the server. Send \dt from a driver and you get a syntax error, because the server has never heard of it.

# psql: works
mydb=# \dt
         List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+--------
 public | orders | table | kevin
 public | users  | table | kevin

# From Python: fails
psycopg.errors.SyntaxError: syntax error at or near "\"

\dt lists tables, and by default only user-created ones: the documentation is explicit that system objects are hidden unless you supply a pattern or the S modifier. \dt+ adds persistence, size on disk and any description.

The catch is search_path. \dt shows tables in the schemas currently on your path, which is why a table you know exists can be missing from the list:

\dt                  -- only schemas on search_path
\dt reporting.*      -- one specific schema
\dt *.*              -- every schema
SHOW search_path;    -- what is actually on the path

From SQL there are two catalogues you can use. The portable one:

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

And the Postgres-specific one, which is faster and gives you more:

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

Use information_schema if the same query has to run against other databases too, since it is a SQL standard. Use pg_catalogwhen you want Postgres-only detail such as the owner or the table's physical size.

\l lists databases with their names, owners, encodings and access privileges. Sizes are not included by default; \l+ adds size, default tablespace and description, and the docs note that size is only available for databases you can actually connect to.

-- the SQL equivalent
SELECT datname FROM pg_database WHERE datistemplate = false ORDER BY datname;

The datistemplate = false filter drops template0 and template1, which exist in every cluster and are rarely what you are looking for.

\dn lists schemas, user-created ones only unless you add S. From SQL:

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name NOT LIKE 'pg_%'
  AND schema_name <> 'information_schema'
ORDER BY schema_name;

A schema is a namespace insidea database. That is the distinction people search for: databases are isolated from one another and need separate connections, while schemas live together and one query can join across them. If you are coming from MySQL, a MySQL "database" behaves much more like a Postgres schema than like a Postgres database.

Which schema you land in is controlled by search_path:

SHOW search_path;
SET search_path TO reporting, public;

\du lists roles, and again only user-created ones by default. Postgres merged users and groups into a single concept called a role, so what you are listing is roles; the ones that can log in are what you would normally call users.

SELECT rolname, rolsuper, rolcanlogin, rolcreatedb
FROM pg_catalog.pg_roles
WHERE rolname NOT LIKE 'pg\_%'
ORDER BY rolname;

Filter on rolcanlogin = true if you want only the roles that are actually login accounts.

\d table_name is the equivalent of MySQL's DESCRIBE, and it shows more: every column with its type and any NOT NULL or default, plus the associated indexes, constraints, rules and triggers. \d+ adds column comments, the view definition if it is a view, and the access method.

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'users'
ORDER BY ordinal_position;

ORDER BY ordinal_position matters. Without it the column order is not guaranteed, and a column list in the wrong order is confusing in exactly the situation where you are trying to understand an unfamiliar table.

If you are arriving from MySQL:

The same idea applies to the other engines, where the answers differ again: see SHOW TABLES in MySQL and listing tables in SQLite. And when one of these queries fails with a Postgres error, our Postgres error library explains the common ones.