System Catalogs, information_schema, psql Introspection, and Metadata Queries

Learning outcomes

When a deployment fails with “relation does not exist,” a migration tool reports an unexpected owner, or a security review asks who can access a schema, guessing from application code is inefficient. PostgreSQL is catalog-driven: the server records databases, schemas, relations, columns, types, functions, roles, privileges, dependencies, extensions, and far more in system catalogs and views.

The challenge is choosing the right interface. information_schema provides SQL-standard-oriented metadata and often filters rows according to what the current user can access. pg_catalog exposes PostgreSQL-specific richness. psql meta-commands are excellent human exploration tools. regclass, regtype, regnamespace, and other reg* types let you resolve object names through PostgreSQL's own parser rules instead of writing fragile name-only catalog joins.

1. Three metadata interfaces, three audiences

A robust tool often prefers documented SQL/catalog interfaces and tests behavior across supported PostgreSQL majors. A human operator may start with psql because it rapidly narrows the question.

2. Start with psql introspection

\conninfo\dn+\dt app.*\d+ app.work_orders\dp app.*\ddp\du\drg\dx+

These commands answer different questions: namespace inventory, relation inventory, table definition/storage details, explicit privileges, default privileges, roles, memberships, and installed extensions. They are not SQL statements and must not be sent through a generic SQL execution API.

3. information_schema: portable vocabulary with visibility rules

The information schema lives in the information_schema schema. It is not normally placed in search_path, so qualify its views. The schemata view shows schemas the current user can access; other views similarly follow SQL-standard visibility concepts.

SELECT schema_name, schema_ownerFROM information_schema.schemataWHERE schema_name IN ('app','extensions','public')ORDER BY schema_name;SELECT table_schema, table_name, table_typeFROM information_schema.tablesWHERE table_schema = 'app'ORDER BY table_name;SELECT table_schema, table_name, ordinal_position,       column_name, data_type, is_nullable, column_defaultFROM information_schema.columnsWHERE table_schema = 'app'ORDER BY table_name, ordinal_position;

Run these first as an administrator and then as servicehub_app. If the row sets differ, that is not catalog corruption; metadata visibility can reflect the current role's access.

4. pg_catalog: PostgreSQL's richer internal dictionary

PostgreSQL stores relation-like objects—ordinary tables, indexes, sequences, views, materialized views, partitioned relations, foreign tables, and more—in pg_class. The containing schema is identified by an OID in pg_namespace. Columns are represented in pg_attribute; types in pg_type. Use documented catalog columns and relation-kind codes for your target major.

SELECT n.nspname AS schema_name,       c.relname AS object_name,       c.relkind,       c.relowner::regrole AS owner,       c.relpersistence,       c.relispartitionFROM pg_catalog.pg_class AS cJOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespaceWHERE n.nspname = 'app'ORDER BY c.relkind, c.relname;

An index appears as its own pg_class row, as does the sequence backing an identity column. That is one reason counting pg_class rows is not the same thing as counting application tables.

5. Columns: avoid the “name-only join” trap

Catalogs identify objects by OID because names are not globally unique. Two schemas can both contain work_orders. A query that filters only relname='work_orders' can return multiple unrelated relations. Resolve the exact relation first, then use its OID:

SELECT a.attnum AS ordinal,       a.attname AS column_name,       pg_catalog.format_type(a.atttypid, a.atttypmod) AS formatted_type,       a.attnotnull AS not_null,       a.atthasdef AS has_defaultFROM pg_catalog.pg_attribute AS aWHERE a.attrelid = 'app.work_orders'::regclass  AND a.attnum > 0  AND NOT a.attisdroppedORDER BY a.attnum;

'app.work_orders'::regclass asks PostgreSQL to resolve a relation name to its OID using correct identifier/qualification rules. The reverse cast, oid::regclass, displays a symbolic relation name, schema-qualified when necessary.

6. OID aliases make catalog queries readable

The base oid type is an internal object identifier. PostgreSQL also provides alias types such as regclass, regtype, regnamespace, regrole, regprocedure, and others. They are especially useful for diagnostics because the input/output functions understand PostgreSQL object naming.

SELECT 'app.work_orders'::regclass AS relation_oid_symbolic,       'app'::regnamespace AS schema_oid_symbolic,       'servicehub_owner'::regrole AS owner_oid_symbolic,       'text'::regtype AS type_oid_symbolic;SELECT c.oid,       c.oid::regclass AS relation,       c.relnamespace::regnamespace AS schema_name,       c.relowner::regrole AS ownerFROM pg_catalog.pg_class AS cWHERE c.oid = 'app.work_orders'::regclass;

Do not store raw OIDs as durable business identifiers. OIDs are server metadata identities and can differ after dump/restore or across clusters. The reg* types are catalog tools, not a replacement for application primary keys.

7. to_regclass() when nonexistence is expected

A cast such as 'app.missing'::regclass raises an error if the name cannot be resolved. In deployment checks, “object does not exist yet” may be a valid branch. Use to_regclass() when NULL is the desired absence signal:

SELECT to_regclass('app.work_orders') AS existing_relation,       to_regclass('app.not_created_yet') AS missing_relation;

That is safer than querying pg_class.relname without schema qualification and accidentally finding a same-named object elsewhere.

8. Object definitions: ask PostgreSQL to reconstruct SQL where supported

Catalog rows are normalized internal metadata, not always the most readable representation. PostgreSQL provides helper functions such as pg_get_viewdef(), pg_get_functiondef(), pg_get_expr(), and pg_get_constraintdef() for reconstructing definitions.

CREATE VIEW app.ch03_open_work_orders ASSELECT work_order_id, customer_id, status, priority, summaryFROM app.work_ordersWHERE status <> 'completed';SELECT pg_catalog.pg_get_viewdef('app.ch03_open_work_orders'::regclass, true);\d+ app.ch03_open_work_orders

Use the server's deparser instead of manually concatenating catalog columns into “equivalent SQL” that may miss quoting, casts, or version-specific syntax.

9. Deliberately wrong approach: build automation by scraping \d output

psql formatting is designed for a terminal and varies with version, expanded mode, locale, terminal width, and psql features. A deployment script that runs \d and uses regular expressions to infer constraints is brittle.

The repair is to use structured SQL interfaces. For portability, start with information_schema. For PostgreSQL-specific features, use documented pg_catalog views/catalogs and helper functions. Record the supported server majors and integration-test queries against each major during upgrades.

10. Hands-on lab: answer ten metadata questions

Create a small investigation sheet and answer these with SQL, not memory:

1. Who owns the app schema?
2. Which ordinary tables exist in app?
3. Which sequences exist there?
4. Who owns app.work_orders?
5. What is its exact primary-key constraint definition?
6. Which columns are nullable?
7. What database roles begin with servicehub?
8. Does servicehub_app have CREATE on schema app?
9. Which extensions are installed?
10. Which relation does unqualified work_orders resolve to under the application path?

SELECT nspname, nspowner::regrole AS ownerFROM pg_catalog.pg_namespaceWHERE nspname = 'app';SELECT has_schema_privilege('servicehub_app','app','CREATE') AS app_can_create,       has_schema_privilege('servicehub_app','app','USAGE') AS app_can_use;SELECT extname, extversion, extowner::regrole AS owner,       extnamespace::regnamespace AS main_schemaFROM pg_catalog.pg_extensionORDER BY extname;SELECT conname, pg_catalog.pg_get_constraintdef(oid, true) AS definitionFROM pg_catalog.pg_constraintWHERE conrelid = 'app.work_orders'::regclassORDER BY conname;

Afterward, drop the disposable view from the earlier section:

DROP VIEW IF EXISTS app.ch03_open_work_orders;

11. Production judgment and next bridge

Metadata queries become operational code. Keep them version-tested, privilege-aware, schema-qualified, and documented. Prefer PostgreSQL's own deparser/identifier functions over home-grown SQL reconstruction. Never update system catalogs directly as an ordinary administration technique.

Lesson 4 uses the same catalog mindset to inspect dependencies. PostgreSQL does not merely know that objects exist; it records which objects depend on which others and uses that graph to prevent unsafe drops.

Authoritative references

• PostgreSQL 18 — System Catalogs
• PostgreSQL 18 — Information Schema
• PostgreSQL 18 — psql
• PostgreSQL 18 — Object Identifier Types
• PostgreSQL 18 — pg_class
• PostgreSQL 18 — pg_attribute
• PostgreSQL 18 — System Information Functions