Postgres - get all schemas, tables, columns, and references

sql.sql

with refs as (
  select kcu.table_schema as table_schema,
    kcu.table_name as table_name,
    kcu.column_name as column_name,
    rel_kcu.table_schema as references_schema,
    rel_kcu.table_name as references_table
  from information_schema.table_constraints tco
  join information_schema.key_column_usage kcu
    on tco.constraint_schema = kcu.constraint_schema
    and tco.constraint_name = kcu.constraint_name
  join information_schema.referential_constraints rco
    on tco.constraint_schema = rco.constraint_schema
    and tco.constraint_name = rco.constraint_name
  join information_schema.key_column_usage rel_kcu
    on rco.unique_constraint_schema = rel_kcu.constraint_schema
    and rco.unique_constraint_name = rel_kcu.constraint_name
    and kcu.ordinal_position = rel_kcu.ordinal_position
  where tco.constraint_type = 'FOREIGN KEY'
    and rel_kcu.column_name = 'id'
    and kcu.table_schema not ilike 'pg_%'
    and kcu.table_schema <> 'information_schema'
)
select
  columns.table_schema,
  columns.table_name,
  columns.column_name,
  columns.is_nullable = 'YES' as is_nullable,
  columns.udt_name as data_type,
  refs.references_schema,
  refs.references_table
from information_schema.columns
left join refs on refs.table_schema = columns.table_schema
  and refs.table_name = columns.table_name
  and refs.column_name = columns.column_name
where columns.table_schema not ilike 'pg_%'
  and columns.table_schema <> 'information_schema'
order by table_schema, table_name, ordinal_position