PostgreSQL Schema Visualizer

WITH excluded AS (
  SELECT unnest(ARRAY['information_schema']) AS s
),
cols AS (
  SELECT
    n.nspname                                            AS schema,
    c.relname                                            AS tbl,
    a.attname                                            AS col,
    pg_catalog.format_type(a.atttypid, a.atttypmod)     AS typ,
    a.attnotnull                                         AS notnull,
    pg_get_expr(d.adbin, d.adrelid)                      AS dflt,
    a.attnum                                             AS pos,
    a.attidentity                                        AS identity_type,
    a.attgenerated                                       AS gen,
    col_description(c.oid, a.attnum)                     AS comment
  FROM pg_class c
  JOIN pg_namespace n    ON n.oid = c.relnamespace
  JOIN pg_attribute a    ON a.attrelid = c.oid
                        AND a.attnum > 0
                        AND NOT a.attisdropped
  LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = a.attnum
  WHERE c.relkind IN ('r', 'p')
    AND NOT c.relispartition
    AND n.nspname NOT LIKE 'pg_%'
    AND n.nspname NOT IN (SELECT s FROM excluded)
),
pks AS (
  SELECT
    n.nspname                                          AS schema,
    c.relname                                          AS tbl,
    array_agg(a.attname ORDER BY k.pos)               AS cols
  FROM pg_index ix
  JOIN pg_class c      ON c.oid = ix.indrelid
  JOIN pg_namespace n  ON n.oid = c.relnamespace
  JOIN LATERAL unnest(ix.indkey::smallint[])
       WITH ORDINALITY AS k(attnum, pos)              ON k.attnum > 0
  JOIN pg_attribute a  ON a.attrelid = c.oid
                       AND a.attnum = k.attnum::int2
  WHERE ix.indisprimary
    AND n.nspname NOT LIKE 'pg_%'
    AND n.nspname NOT IN (SELECT s FROM excluded)
  GROUP BY n.nspname, c.relname
),
uqs AS (
  SELECT
    n.nspname                                          AS schema,
    c.relname                                          AS tbl,
    array_agg(a.attname ORDER BY k.pos)               AS cols
  FROM pg_index ix
  JOIN pg_class c      ON c.oid = ix.indrelid
  JOIN pg_namespace n  ON n.oid = c.relnamespace
  JOIN LATERAL unnest(ix.indkey::smallint[])
       WITH ORDINALITY AS k(attnum, pos)              ON k.attnum > 0
  JOIN pg_attribute a  ON a.attrelid = c.oid
                       AND a.attnum = k.attnum::int2
  WHERE ix.indisunique AND NOT ix.indisprimary
    AND ix.indpred IS NULL
    AND ix.indexprs IS NULL
    AND EXISTS (SELECT 1 FROM pg_constraint cc
                WHERE cc.conindid = ix.indexrelid AND cc.contype = 'u')
    AND n.nspname NOT LIKE 'pg_%'
    AND n.nspname NOT IN (SELECT s FROM excluded)
  GROUP BY n.nspname, c.relname, ix.indexrelid
),
fks AS (
  SELECT
    n.nspname                                          AS schema,
    c.relname                                          AS tbl,
    array_agg(a.attname  ORDER BY k.pos)              AS cols,
    fn.nspname                                         AS ref_schema,
    fc.relname                                         AS ref_tbl,
    array_agg(fa.attname ORDER BY k.pos)              AS ref_cols,
    con.confdeltype                                    AS on_delete,
    con.confupdtype                                    AS on_update,
    con.conname                                        AS name,
    min(a.attnum) FILTER (WHERE k.pos = 1)             AS pos
  FROM pg_constraint con
  JOIN pg_class c      ON c.oid  = con.conrelid
  JOIN pg_namespace n  ON n.oid  = c.relnamespace
  JOIN pg_class fc     ON fc.oid = con.confrelid
  JOIN pg_namespace fn ON fn.oid = fc.relnamespace
  JOIN LATERAL unnest(con.conkey)
       WITH ORDINALITY AS k(attnum, pos)              ON TRUE
  JOIN LATERAL unnest(con.confkey)
       WITH ORDINALITY AS fk(attnum, pos)             ON fk.pos = k.pos
  JOIN pg_attribute a  ON a.attrelid  = c.oid  AND a.attnum  = k.attnum
  JOIN pg_attribute fa ON fa.attrelid = fc.oid AND fa.attnum = fk.attnum
  WHERE con.contype = 'f'
    AND n.nspname NOT LIKE 'pg_%'
    AND n.nspname NOT IN (SELECT s FROM excluded)
    AND fn.nspname NOT LIKE 'pg_%'
    AND fn.nspname NOT IN (SELECT s FROM excluded)
  GROUP BY n.nspname, c.relname, fn.nspname, fc.relname,
           con.confdeltype, con.confupdtype, con.conname, con.oid
),
enums AS (
  SELECT
    n.nspname                                              AS schema,
    t.typname                                              AS name,
    array_agg(e.enumlabel ORDER BY e.enumsortorder)       AS vals
  FROM pg_type t
  JOIN pg_namespace n ON n.oid = t.typnamespace
  JOIN pg_enum e      ON e.enumtypid = t.oid
  WHERE t.typtype = 'e'
    AND n.nspname NOT LIKE 'pg_%'
    AND n.nspname NOT IN (SELECT s FROM excluded)
  GROUP BY n.nspname, t.typname
),
tchks AS (
  SELECT
    n.nspname                                              AS schema,
    c.relname                                              AS tbl,
    con.conname                                            AS name,
    pg_get_expr(con.conbin, con.conrelid)                  AS expr
  FROM pg_constraint con
  JOIN pg_class c      ON c.oid = con.conrelid
  JOIN pg_namespace n  ON n.oid = c.relnamespace
  WHERE con.contype = 'c'
    AND n.nspname NOT LIKE 'pg_%'
    AND n.nspname NOT IN (SELECT s FROM excluded)
),
idxs AS (
  SELECT
    n.nspname                                          AS schema,
    c.relname                                          AS tbl,
    ic.relname                                         AS name,
    ix.indisunique                                     AS is_unique,
    array_agg(a.attname ORDER BY k.pos)               AS cols
  FROM pg_index ix
  JOIN pg_class c      ON c.oid = ix.indrelid
  JOIN pg_class ic     ON ic.oid = ix.indexrelid
  JOIN pg_namespace n  ON n.oid = c.relnamespace
  JOIN LATERAL unnest(ix.indkey::smallint[])
       WITH ORDINALITY AS k(attnum, pos)              ON k.attnum > 0
  JOIN pg_attribute a  ON a.attrelid = c.oid
                       AND a.attnum = k.attnum::int2
  WHERE NOT ix.indisprimary
    AND ix.indpred IS NULL
    AND ix.indexprs IS NULL
    AND NOT EXISTS (SELECT 1 FROM pg_constraint cc
                    WHERE cc.conindid = ix.indexrelid)
    AND n.nspname NOT LIKE 'pg_%'
    AND n.nspname NOT IN (SELECT s FROM excluded)
  GROUP BY n.nspname, c.relname, ic.relname, ix.indisunique, ix.indexrelid
),
tcoms AS (
  SELECT
    n.nspname                                          AS schema,
    c.relname                                          AS tbl,
    obj_description(c.oid, 'pg_class')                 AS comment
  FROM pg_class c
  JOIN pg_namespace n  ON n.oid = c.relnamespace
  WHERE c.relkind IN ('r', 'p')
    AND NOT c.relispartition
    AND n.nspname NOT LIKE 'pg_%'
    AND n.nspname NOT IN (SELECT s FROM excluded)
),
tbls AS (
  SELECT DISTINCT schema, tbl FROM cols ORDER BY schema, tbl
)
SELECT json_build_object(
  'tables', (
    SELECT json_agg(
      json_build_object(
        'schema',  t.schema,
        'name',    t.tbl,
        'columns', (
          SELECT json_agg(json_build_object(
            'name',          c.col,
            'type',          c.typ,
            'not_null',      c.notnull,
            'default',       c.dflt,
            'identity_type', c.identity_type,
            'generated',     c.gen,
            'comment',       c.comment
          ) ORDER BY c.pos)
          FROM cols c
          WHERE c.schema = t.schema AND c.tbl = t.tbl
        ),
        'primary_key',        (SELECT p.cols FROM pks p
                                WHERE p.schema = t.schema AND p.tbl = t.tbl),
        'unique_constraints', (SELECT json_agg(u.cols)
                                FROM uqs u
                                WHERE u.schema = t.schema AND u.tbl = t.tbl),
        'foreign_keys',       (SELECT json_agg(json_build_object(
                                  'columns',     f.cols,
                                  'ref_schema',  f.ref_schema,
                                  'ref_table',   f.ref_tbl,
                                  'ref_columns', f.ref_cols,
                                  'on_delete',   f.on_delete,
                                  'on_update',   f.on_update
                                ) ORDER BY
                                  CASE WHEN array_length(f.cols, 1) = 1
                                       THEN f.pos ELSE 32767 END,
                                  f.name)
                                FROM fks f
                                WHERE f.schema = t.schema AND f.tbl = t.tbl),
        'comment',            (SELECT tc.comment FROM tcoms tc
                                WHERE tc.schema = t.schema AND tc.tbl = t.tbl),
        'checks',             (SELECT json_agg(json_build_object(
                                  'name',       ch.name,
                                  'expression', ch.expr
                                ))
                                FROM tchks ch
                                WHERE ch.schema = t.schema AND ch.tbl = t.tbl),
        'indexes',            (SELECT json_agg(json_build_object(
                                  'name',    i.name,
                                  'columns', i.cols,
                                  'unique',  i.is_unique
                                ))
                                FROM idxs i
                                WHERE i.schema = t.schema AND i.tbl = t.tbl)
      )
    )
    FROM tbls t
  ),
  'enums', (
    SELECT json_agg(json_build_object(
      'schema', e.schema,
      'name',   e.name,
      'values', e.vals
    ) ORDER BY e.schema, e.name)
    FROM enums e
  )
) AS result;