The essentials
Quick reference
One focused task per row. Jump to the related section for complete, working examples.
| Use | Syntax | Examples |
|---|---|---|
| List visible tables | SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'; | View examples |
| List PostgreSQL relations | SELECT oid::regclass, relkind
FROM pg_catalog.pg_class
WHERE relnamespace = 'app'::regnamespace; | View examples |
| Resolve the catalog schema | SELECT pg_catalog.current_schema(); | View examples |
| Resolve a relation OID | SELECT to_regclass('app.orders'); | View examples |
| Find a relation kind | SELECT relkind
FROM pg_class
WHERE oid = 'app.orders'::regclass; | View examples |
| List portable columns | SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'app' AND table_name = 'orders'; | View examples |
| Render a PostgreSQL type | SELECT format_type(atttypid, atttypmod)
FROM pg_attribute
WHERE attrelid = 'app.orders'::regclass AND attnum > 0; | View examples |
| Resolve a type safely | SELECT to_regtype('pg_catalog.int8'); | View examples |
| Show index definitions | SELECT indexrelid::regclass,
indisvalid,
pg_get_indexdef(indexrelid)
FROM pg_index
WHERE indrelid = 'app.orders'::regclass; | View examples |
| Show a constraint definition | SELECT pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conname = 'orders_pkey'; | View examples |
| Check table privilege | SELECT has_table_privilege('app_reader', 'app.orders', 'SELECT'); | View examples |
| Check schema usage | SELECT has_schema_privilege('app_reader', 'app', 'USAGE'); | View examples |
| Check function execution | SELECT has_function_privilege('app_reader', 'app.total(bigint)', 'EXECUTE'); | View examples |
| Describe a catalog object | SELECT pg_describe_object('pg_class'::regclass, 'app.orders'::regclass, 0); | View examples |
| Inspect dependencies | SELECT *
FROM pg_depend
WHERE refobjid = 'app.orders'::regclass; | View examples |
| Check server version number | SELECT current_setting('server_version_num')::int; | View examples |
| Check recovery state | SELECT pg_is_in_recovery(); | View examples |
information_schema offers SQL-standard, privilege-filtered metadata, while pg_catalog exposes PostgreSQL-specific details needed for operations and tooling. Catalogs are regular relations but direct writes are unsafe; prefer DDL, supported functions, and explicitly selected columns that tolerate version evolution.
Step by step
Detailed examples
Choose portability or PostgreSQL detail intentionally
information_schema uses standardized names and exposes only objects the current user can access, making it a good default for portable discovery. pg_catalog includes internal identifiers and PostgreSQL features. Neither is a stable promise for SELECT *, so enumerate columns and test tooling against every supported major release.
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;Join pg_class to pg_namespace for relation identity
pg_class describes tables, indexes, sequences, views, materialized views, composite types, and partitioned objects; relkind distinguishes them. Names are unique only within a schema, while OIDs can change after dump/restore. Cast to regclass for readable identity and safe name resolution.
SELECT c.oid::regclass AS relation, c.relkind, c.relpersistence,
c.reltuples, c.relpages
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'app'
ORDER BY n.nspname, c.relname;Use attnum and dropped-column filters correctly
pg_attribute includes system and dropped columns, so filter attnum > 0 and NOT attisdropped. format_type renders type modifiers such as varchar lengths and numeric precision. information_schema.columns separates domains and underlying UDT identity and is preferable for portable clients.
SELECT a.attnum, a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type,
a.attnotnull, a.attidentity, a.attgenerated
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = 'app.orders'::regclass
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;Inspect constraints and indexes without parsing DDL
pg_constraint connects constraints to relations and pg_index holds index-specific flags and column mappings. pg_get_constraintdef and pg_get_indexdef reconstruct readable definitions, but generated text is for display and migration assistance rather than a cross-version serialization format. Validity flags matter during concurrent builds and staged validation.
SELECT conname, contype, convalidated,
pg_catalog.pg_get_constraintdef(oid, true) AS definition
FROM pg_catalog.pg_constraint
WHERE conrelid = 'app.orders'::regclass
ORDER BY conname;Ask privilege functions instead of inferring ACL text
ACL arrays are compact internal representations and ownership, inheritance, PUBLIC grants, and column grants complicate manual interpretation. has_*_privilege functions answer effective privilege questions for the current role or a named role. Metadata visibility can itself disclose structure, so grant pg_read_all_settings or monitoring roles deliberately.
SELECT has_table_privilege('app_reader', 'app.orders', 'SELECT') AS can_read,
has_table_privilege('app_writer', 'app.orders', 'INSERT, UPDATE') AS can_write;Use dependency catalogs to explain ownership and drop behavior
pg_depend records database-local dependencies between objects and pg_shdepend covers dependencies on cluster-wide objects such as roles and tablespaces. deptype distinguishes normal, automatic, internal, extension, and other dependency semantics. Catalog joins are version-specific; use pg_describe_object for readable output and never edit dependency rows.
SELECT d.deptype,
pg_catalog.pg_describe_object(d.classid, d.objid, d.objsubid) AS dependent
FROM pg_catalog.pg_depend d
WHERE d.refclassid = 'pg_class'::regclass
AND d.refobjid = 'app.orders'::regclass
ORDER BY dependent;Treat catalog access as read-only and version-aware
Direct catalog updates can corrupt metadata and are unsupported even though catalogs are tables. System catalogs are generally database-local, with selected shared catalogs such as pg_database and role catalogs. Standbys expose replayed catalog state read-only; monitoring views can differ from primary state and may be privilege-filtered.
SELECT current_setting('server_version_num')::int AS server_version_num,
current_database(), current_user, pg_is_in_recovery();Sources and further reading
References
Authoritative documentation used to verify and expand this cheat sheet.
- PostgreSQL Global Development GroupSystem Catalogspostgresql.org
- PostgreSQL Global Development GroupSystem Catalog Overviewpostgresql.org
- PostgreSQL Global Development GroupThe Information Schemapostgresql.org
- PostgreSQL Global Development Groupinformation_schema.tablespostgresql.org
- PostgreSQL Global Development Groupinformation_schema.columnspostgresql.org
- PostgreSQL Global Development Grouppg_classpostgresql.org
- PostgreSQL Global Development Grouppg_attributepostgresql.org
- PostgreSQL Global Development Grouppg_dependpostgresql.org
- PostgreSQL Global Development GroupSystem Information Functionspostgresql.org
Help us improve
Found a typo or missing example?
Tell us what would make this cheat sheet clearer, more complete, or more useful.