System Catalog

System Catalog

DB9 implements PostgreSQL-compatible system catalog views for introspecting database objects.

pg_catalog Views

View	Description
pg_tables	User tables
pg_views	User views
pg_class	Tables, indexes, sequences, views
pg_attribute	Table columns
pg_attrdef	Column default values
pg_namespace	Schemas
pg_type	Data types
pg_index	Index metadata
pg_indexes	Index definitions (indexdef)
pg_constraint	Constraints (PK, FK, CHECK, UNIQUE, NOT NULL)
pg_proc	Functions and procedures
pg_trigger	Triggers — no tgtype column, and pg_get_triggerdef() does not exist; read the declared timing and row/statement level from information_schema.triggers (action_timing, action_orientation) instead
pg_enum	Enum type values
pg_sequence	Sequence metadata
pg_extension	Installed extensions
pg_collation	Collations — built-ins only; collations you create work but never appear
pg_roles / pg_user	User and role definitions
pg_auth_members	Role membership: built-in rows making pg_monitor a member of pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables, plus any GRANT <role> TO <role> you issue. REVOKE removes the row again
pg_db_role_setting	Per-role parameter defaults written by ALTER ROLE … [IN DATABASE …] SET (also shown in pg_roles.rolconfig); applied to new sessions for that role
pg_database	Databases
pg_description	Object descriptions/comments
pg_depend	Dependency tracking
pg_am	Access methods
pg_stat_user_tables	Table statistics
pg_inherits	Table inheritance
pg_range	Range type metadata
pg_opclass	Operator classes
pg_policy	Row-level security policies
pg_policies	Readable RLS policy view (qual, with_check)
pg_settings	Runtime parameters, including DB9’s db9.* tuning settings
pg_timezone_names	Time zone names, abbreviations and UTC offsets (name, abbrev, utc_offset, is_dst)
pg_rewrite	Rewrite rules — populated with the _RETURN rule backing each view
pg_aggregate	Aggregate functions. Exposes only aggfnoid; the transition-function columns are absent
pg_publication_rel	Publication table membership, written by ALTER PUBLICATION ... ADD/DROP/SET TABLE. The rows persist but nothing is replicated

The following relations resolve but are always empty — they exist so that catalog-introspecting tools and ORMs do not error, not because the feature behind them is implemented:

Relation	Note
pg_cast	No user-defined casts
pg_default_acl	No default privilege rules
pg_shdescription	No shared-object comments
pg_publication_namespace	ALTER PUBLICATION ... ADD TABLES IN SCHEMA is rejected, so this is never populated
pg_replication_slots	19 columns, including conflicting plus the PostgreSQL 17 additions failover/synced. pg_create_logical_replication_slot() exists in the FROM clause only — as SELECT pg_create_logical_replication_slot(...) it reports 42883 does not exist. Where it does resolve it still cannot create a slot: wal2json ends at 55000 logical replication is not provisioned for this database, other plugins at 22023. See the full call table. This view stays empty either way
pg_statistic_ext	No extended statistics objects

information_schema Views

View	Description
tables	Tables
columns	Table columns (includes is_generated, generation_expression)
schemata	Schemas
sequences	Sequences
routines	Functions and procedures
table_constraints	PRIMARY KEY, FOREIGN KEY, CHECK and UNIQUE constraints (never NOT NULL — see below)
key_column_usage	PRIMARY/FOREIGN KEY columns
referential_constraints	Foreign key constraints
check_constraints	CHECK constraints
constraint_column_usage	Column constraint usage
table_privileges	Table access privileges
triggers	Triggers, with the declared action_timing (BEFORE / AFTER) and action_orientation (ROW / STATEMENT)

These twelve views are the complete information_schema surface. Notably, information_schema.views is not implemented — it fails with relation "views" does not exist (42P01). List views through pg_views, or filter information_schema.tables on table_type = 'VIEW'.

Cron Catalog

View	Description
cron.job	Scheduled cron jobs
cron.job_run_details	Job execution history
cron.running_jobs	Currently executing jobs (superuser only) — see pg_cron

---

Query Recipes

Practical queries for introspecting your database. Replace 'public' and 'my_table' with your actual schema and table names.

List all tables in a schema

Returns all user-defined tables in a schema with their owner and row estimate.

SQL

SELECT
  tablename        AS table,
  tableowner       AS owner,
  hasindexes       AS indexed,
  hasrules         AS has_rules,
  hastriggers      AS has_triggers
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;

-- Alternative using information_schema (more portable):
SELECT
  table_name,
  table_type
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_type = 'BASE TABLE'
ORDER BY table_name;

▶ Run

List columns of a table

Returns all columns with their data type, nullability, and default value.

SQL

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

-- Using pg_attribute for lower-level detail (includes system columns):
SELECT
  a.attname                               AS column,
  pg_catalog.format_type(a.atttypid, a.atttypmod) AS type,
  a.attnotnull                            AS not_null,
  a.attnum                                AS position
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relname = 'my_table'
  AND a.attnum  > 0          -- exclude system columns
  AND NOT a.attisdropped
ORDER BY a.attnum;

Find all indexes on a table

Lists every index on a table, including the columns covered and whether it is unique or primary.

SQL

SELECT
  indexname   AS index,
  indexdef    AS definition
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename  = 'my_table'
ORDER BY indexname;

-- More detailed view via pg_index + pg_class:
SELECT
  i.relname                                   AS index,
  ix.indisunique                              AS unique,
  ix.indisprimary                             AS primary,
  array_agg(a.attname ORDER BY a.attnum)      AS columns
FROM pg_index     ix
JOIN pg_class     t  ON t.oid  = ix.indrelid
JOIN pg_class     i  ON i.oid  = ix.indexrelid
JOIN pg_attribute a  ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey)
JOIN pg_namespace n  ON n.oid  = t.relnamespace
WHERE n.nspname = 'public'
  AND t.relname = 'my_table'
GROUP BY i.relname, ix.indisunique, ix.indisprimary
ORDER BY i.relname;

Check table constraints

The information_schema query returns the PRIMARY KEY, UNIQUE, CHECK and FOREIGN KEY constraints on a table; the pg_constraint variant additionally returns NOT NULL.

SQL

SELECT
  tc.constraint_name,
  tc.constraint_type,
  kcu.column_name,
  cc.check_clause
FROM information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu
  ON kcu.constraint_name = tc.constraint_name
  AND kcu.table_schema   = tc.table_schema
LEFT JOIN information_schema.check_constraints cc
  ON cc.constraint_name  = tc.constraint_name
  AND cc.constraint_schema = tc.constraint_schema
WHERE tc.table_schema = 'public'
  AND tc.table_name   = 'my_table'
ORDER BY tc.constraint_type, tc.constraint_name;

-- Concise version using pg_constraint:
SELECT
  conname                     AS constraint,
  contype                     AS type,  -- p=PK, u=UNIQUE, c=CHECK, f=FK, n=NOT NULL
  pg_get_constraintdef(oid)   AS definition
FROM pg_constraint
WHERE conrelid = 'public.my_table'::regclass
ORDER BY contype, conname;

Every pg_constraint query fails if a referenced table was dropped

Dropping a table that a foreign key points at is permitted, and the orphaned constraint makes every pg_constraint scan in the database fail with internal error (XX000) — including the query above, and including queries about unrelated tables.

information_schema.table_constraints — the first query above — keeps working, but not the whole schema: referential_constraints and constraint_column_usage fail with the same XX000, which between them are the views that would have told you which referenced table went missing. See DDL — DROP statements for the way out.

NOT NULL appears only in pg_constraint

The two queries above do not return the same set. information_schema.table_constraints emits only PRIMARY KEY, FOREIGN KEY, CHECK and UNIQUE — DB9 never surfaces a NOT NULL row there, unlike PostgreSQL, which reports each one as a generated CHECK.

pg_constraint does expose them, as contype = 'n' with a <table>_<column>_not_null name, so the concise query returns one extra row per NOT NULL column.

Which PostgreSQL you are porting from matters here. PostgreSQL only records NOT NULL in pg_constraint as of version 18; PostgreSQL 16 — the version DB9 reports in version() — has no contype = 'n' rows at all, so a query calibrated against PG16 will see rows it does not expect. A query written for PG18 ports over as-is, with one difference: PG18 preserves a user-supplied NOT NULL constraint name, and DB9 does not. CONSTRAINT my_nn NOT NULL is stored as <table>_<column>_not_null regardless. Explicit PRIMARY KEY, UNIQUE and CHECK names are preserved as written.

To list nullability, prefer information_schema.columns.is_nullable or pg_attribute.attnotnull over either constraint view.

See RLS policies

Lists all row-level security policies on a table, including which roles they apply to and their USING / WITH CHECK expressions.

SQL

SELECT
  policyname      AS policy,
  cmd             AS command,   -- ALL, SELECT, INSERT, UPDATE, DELETE
  roles           AS applies_to,
  qual            AS using_expr,
  with_check      AS check_expr
FROM pg_policies
WHERE schemaname = 'public'
  AND tablename  = 'my_table'
ORDER BY policyname;

-- Check if RLS is enabled on the table:
SELECT relname, relrowsecurity, relforcerowsecurity
FROM pg_class
WHERE oid = 'public.my_table'::regclass;

Find foreign keys

Returns all foreign key relationships — both outbound (this table references another) and inbound (other tables reference this one).

SQL

-- Outbound: FK constraints on my_table
SELECT
  tc.constraint_name,
  kcu.column_name            AS fk_column,
  ccu.table_name             AS references_table,
  ccu.column_name            AS references_column,
  rc.update_rule,
  rc.delete_rule
FROM information_schema.table_constraints     tc
JOIN information_schema.key_column_usage      kcu ON kcu.constraint_name = tc.constraint_name
  AND kcu.table_schema = tc.table_schema
JOIN information_schema.referential_constraints rc ON rc.constraint_name = tc.constraint_name
  AND rc.constraint_schema = tc.constraint_schema
JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = rc.unique_constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_schema    = 'public'
  AND tc.table_name      = 'my_table'
ORDER BY tc.constraint_name;

-- All FK constraints in the schema (both directions):
SELECT
  c.conrelid::regclass::text  AS from_table,
  c.confrelid::regclass::text AS to_table,
  c.conname                   AS constraint,
  pg_get_constraintdef(c.oid) AS definition
FROM pg_constraint c
JOIN pg_class     t ON t.oid = c.conrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE c.contype = 'f'
  AND n.nspname = 'public'
ORDER BY from_table, c.conname;

Pitfall: cast regclass to text, and filter the schema by join

Two things to watch when adapting regclass queries. Both fail silently rather than erroring:

• DB9 difference — ::regclass alone renders the numeric OID, not the relation name. SELECT conrelid::regclass returns 10000000005, where PostgreSQL returns child_t. Add ::text to get the name.
• regclass::text is schema-qualified only when the relation is not visible in search_path. With the default "$user", public, a table in public renders bare as child_t while a table in another schema renders as other_schema.t. So the common idiom WHERE conrelid::regclass::text LIKE 'public.%' returns zero rows on a schema that does have foreign keys — and under a non-default search_path it is worse than useless: a public table shadowed by a same-named one earlier in the path renders qualified and does match, so the filter silently returns an arbitrary subset. Matching on the bare name instead is no better — it breaks as soon as a table lives outside search_path, or has a name that needs quoting (public."Mix Ed" renders as "Mix Ed", quotes included). This idiom is equally broken on PostgreSQL; it is a widespread bug, not a DB9 limitation. Join to pg_class and pg_namespace and filter on nspname instead, as shown above.

Casting in the other direction is unaffected — WHERE conrelid = 'public.my_table'::regclass works as written, including for tables outside search_path.

List all functions

Returns all user-defined functions in a schema with their return types.

SQL

SELECT
  routine_name         AS function,
  routine_type         AS type,
  data_type            AS return_type
FROM information_schema.routines
WHERE routine_schema = 'public'
ORDER BY routine_name;

-- More detail via pg_proc:
SELECT
  p.proname                        AS function,
  format_type(p.prorettype, NULL)  AS return_type,
  p.prokind                        AS kind        -- 'f' = function, 'p' = procedure, 'w' = window
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'public'
ORDER BY p.proname;

Note

pg_proc exposes 18 columns: oid, proname, pronamespace, proowner, prorettype, prokind, prosecdef, proacl, pronargs, pronargdefaults, proargtypes, proargmodes, proargnames, proretset, prorows, provolatile, provariadic and proconfig.

Argument-level introspection works for user-defined functions — a function declared add_two(a int, b int) reports pronargs = 2, proargtypes = 23 23 and proargnames = {a,b}:

SQL

SELECT proname, pronargs, proargtypes, proargnames
FROM pg_proc WHERE proname = 'add_two';

This is reliable only for functions you created. For catalog built-ins the argument columns are not trustworthy, in three separate ways:

• proargnames, proargmodes and provolatile are NULL for every pg_catalog row — test them with IS NULL, since proargnames = '{}' matches nothing.
• proargtypes is frequently an empty array even when pronargs says there are arguments — age, array_length, concat and array_to_string are all like this, and they are roughly half of pg_catalog. Test it with cardinality(proargtypes) = 0; the comparison proargtypes = '{}' fails with an internal error. Don’t rely on a fixed row count: the number depends on which extensions the database has, not just on the server version — see below.
• pronargs itself can be wrong. count, make_interval and the json_build_* / jsonb_build_* family report pronargs = 0 while accepting arguments, and variadic functions under-report: concat says 1 but concat('a','b','c','d','e') works. The extensions.http* family (all pronargs = 0, taking one to four arguments) is one instance of this, not a special case.

Where arity or argument types matter for a built-in, confirm them by calling the function.

Still unavailable: pg_get_function_arguments(), pg_get_function_result(), pg_proc.prosrc, and the pg_language catalog. Use information_schema.routines for a portable summary.

pg_proc is not a complete list of callable functions

Many built-ins are implemented in the executor and are not registered in the catalog. SUM, GREATEST, SUBSTR, COALESCE, GENERATE_SERIES and UNNEST are all absent from pg_proc yet work normally. Catalog absence is never evidence that a function is missing — call it instead.

Registration is not fixed for a given server build either — it is per database. CREATE EXTENSION fs9 adds its fs9_* functions to pg_proc, so two databases on the same build report different counts. Mind the namespace when you check: those functions land in both pg_catalog and extensions, so a bare proname LIKE 'fs9%' counts more of them than a query filtered to pg_catalog does. ABS is registered today (five rows, one per numeric input type) though this page previously listed it among the unregistered built-ins. Neither absence nor presence is a stable property — call the function.

The rows an extension adds carry no argument metadata: every fs9_* entry has an empty proargtypes, so installing an extension raises the count of catalog rows you cannot introspect without raising the count you can.

Check table sizes

Returns disk usage for every table, largest first. DB9 reports sizes through the storage accounting virtual tables rather than the PostgreSQL size functions.

SQL

SELECT
  table_name,
  data_bytes,
  index_bytes,
  total_bytes,
  round(total_bytes::numeric / 1024 / 1024, 2) AS total_mb
FROM _DB9_SYS_TABLE_STORAGE_STATS
ORDER BY total_bytes DESC;

-- Database-level totals:
SELECT
  database_name,
  total_bytes,
  round(total_bytes::numeric / 1024 / 1024, 2) AS total_mb,
  scanned_at
FROM _DB9_SYS_STORAGE_STATS;

DB9 Difference: PostgreSQL size functions do not exist

pg_size_pretty(), pg_total_relation_size(), pg_relation_size(), pg_table_size() and pg_indexes_size() are not implemented — calling any of them fails with function <name>(...) does not exist (42883), in both the bare and extensions.-qualified forms. Use the storage virtual tables above, which report raw BIGINT byte counts you can format yourself.