Some tricks in postgres to validate internal references. Helpful for schema audits.

postgres_tricks.md

-- These queries can be used to verify schema migrations.

-- Get all enum types in specific schemas
SELECT n.nspname AS schema, t.typname AS enum_name
FROM pg_type t
JOIN pg_namespace n ON n.oid = t.typnamespace
WHERE t.typtype = 'e'
  AND n.nspname IN ('schema1','schema2','schema3', 'public');

-- Get duplicate enums across schemas
SELECT t.typname, COUNT(*)
FROM pg_type t
JOIN pg_namespace n ON n.oid = t.typnamespace
WHERE t.typtype = 'e'
  AND n.nspname IN ('schema1', 'schema2', 'schema3', 'public')
GROUP BY t.typname
HAVING COUNT(*) > 1;

-- List all enums with schema
SELECT n.nspname AS schemaname, t.typname
FROM pg_type t
JOIN pg_namespace n ON n.oid = t.typnamespace
WHERE t.typtype = 'e'
  AND n.nspname IN ('schema1','schema2','schema3', 'public')
ORDER BY n.nspname, t.typname;

-- Get all triggers
SELECT event_object_table, trigger_name, action_statement
FROM information_schema.triggers
WHERE trigger_schema IN ('schema1', 'schema2', 'schema3', 'public');

-- Get all functions by name
SELECT n.nspname AS schema_name,
       p.proname AS function_name,
       pg_get_functiondef(p.oid) AS definition
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE p.proname IN ('function1','function2','function3');

-- Get all materialized views
SELECT matviewname, definition
FROM pg_matviews
WHERE schemaname IN ('schema1','schema2','schema3', 'public');

-- Get all views
SELECT table_schema, table_name, view_definition
FROM information_schema.views
WHERE table_schema IN ('schema1','schema2','schema3', 'public');

-- Get all sequences
SELECT sequence_schema, sequence_name, *
FROM information_schema.sequences
WHERE sequence_schema IN ('schema1','schema2','schema3', 'public');

-- Get all objects and references in the schema
SELECT refobjid::regclass AS dependent_object, objid::regclass AS depends_on
FROM pg_depend
WHERE refobjid::regclass::text LIKE 'schema1.%'
   OR refobjid::regclass::text LIKE 'schema2.%'
   OR refobjid::regclass::text LIKE 'schema3.%'
   OR refobjid::regclass::text LIKE 'public.%';