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_constraint`	Constraints (PK, FK, CHECK, UNIQUE)
`pg_proc`	Functions and procedures
`pg_trigger`	Triggers
`pg_enum`	Enum type values
`pg_sequence`	Sequence metadata
`pg_extension`	Installed extensions
`pg_collation`	Collations
`pg_roles` / `pg_user`	User and role definitions
`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

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`	All table constraints
`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

Cron Catalog

View	Description
`cron.job`	Scheduled cron jobs
`cron.job_run_details`	Job execution history

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.

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;

List columns of a table

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

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.

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

Returns all constraints (PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY, NOT NULL) on a table.

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
  pg_get_constraintdef(oid)   AS definition
FROM pg_constraint
WHERE conrelid = 'public.my_table'::regclass
ORDER BY contype, conname;

See RLS policies

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

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).

-- 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
  conrelid::regclass  AS from_table,
  confrelid::regclass AS to_table,
  conname             AS constraint,
  pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE contype = 'f'
  AND conrelid::regclass::text LIKE 'public.%'
ORDER BY from_table, conname;

List all functions

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

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

-- More detail via pg_proc:
SELECT
  p.proname                           AS function,
  pg_get_function_arguments(p.oid)    AS arguments,
  pg_get_function_result(p.oid)       AS returns,
  l.lanname                           AS language,
  p.prosrc                            AS body
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
JOIN pg_language l  ON l.oid = p.prolang
WHERE n.nspname = 'public'
ORDER BY p.proname;

Check table sizes

Returns disk usage for every table in a schema, sorted largest first.

SELECT
  tablename                                           AS table,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(pg_relation_size(schemaname || '.' || tablename))       AS table_size,
  pg_size_pretty(
    pg_total_relation_size(schemaname || '.' || tablename) -
    pg_relation_size(schemaname || '.' || tablename)
  )                                                   AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

-- Quick size check for a single table:
SELECT
  pg_size_pretty(pg_total_relation_size('public.my_table')) AS total,
  pg_size_pretty(pg_relation_size('public.my_table'))       AS heap,
  pg_size_pretty(pg_indexes_size('public.my_table'))        AS indexes;