Get a data table of entities, fields, and types out of a postgres schema

data-schema-from-postgres.sql

-- Roughly get a entity/field/type table from a parameterized schema
-- A bit buggy, as it currently duplicates some rows (particularly join tables)
SELECT
  cols.table_name,
  cols.column_name,
  cols.ordinal_position,
  cols.column_default,
  cols.data_type,
  CASE WHEN pks.column_name IS NOT NULL
    THEN TRUE
  ELSE FALSE END AS is_primary,
  CASE WHEN fks.foreign_column_name IS NOT NULL
    THEN TRUE
  ELSE FALSE END AS is_reference,
  fks.foreign_table_name,
  fks.foreign_column_name,
  cols.character_maximum_length,
  cols.character_octet_length,
  cols.numeric_precision,
  cols.numeric_precision_radix,
  cols.numeric_scale,
  cols.datetime_precision

FROM information_schema.columns AS cols
  LEFT JOIN (
              SELECT
                tc.table_schema,
                tc.constraint_name,
                tc.table_name,
                kcu.column_name,
                ccu.table_schema AS foreign_table_schema,
                ccu.table_name   AS foreign_table_name,
                ccu.column_name  AS foreign_column_name
              FROM
                information_schema.table_constraints AS tc
                JOIN information_schema.key_column_usage AS kcu
                  ON tc.constraint_name = kcu.constraint_name
                     AND tc.table_schema = kcu.table_schema
                JOIN information_schema.constraint_column_usage AS ccu
                  ON ccu.constraint_name = tc.constraint_name
                     AND ccu.table_schema = tc.table_schema
              WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_schema = :schema
            ) fks ON fks.table_name = cols.table_name AND fks.column_name = cols.column_name
  LEFT JOIN (
              SELECT
                tc.table_schema,
                tc.constraint_name,
                tc.table_name,
                kcu.column_name
              FROM
                information_schema.table_constraints AS tc
                JOIN information_schema.key_column_usage AS kcu
                  ON tc.constraint_name = kcu.constraint_name
                     AND tc.table_schema = kcu.table_schema
                JOIN information_schema.constraint_column_usage AS ccu
                  ON ccu.constraint_name = tc.constraint_name
                     AND ccu.table_schema = tc.table_schema
              WHERE tc.constraint_type = 'PRIMARY KEY' AND tc.table_schema = :schema
            ) pks ON pks.table_name = cols.table_name AND pks.column_name = cols.column_name
WHERE cols.table_schema = :schema;