Find missing foreign key constraints

missing_foreign_key_constraints_spec.sql

SELECT CONCAT(col.table_name, '.', col.column_name)
FROM information_schema.columns col
INNER JOIN pg_catalog.pg_tables tbl ON tbl.schemaname = col.table_schema AND tbl.tablename = col.table_name
LEFT JOIN information_schema.columns col_type ON (
  col.table_schema = col_type.table_schema AND
  col.table_name = col_type.table_name AND
  regexp_replace(col.column_name, '_id$', '_type') = col_type.column_name
)
LEFT JOIN (
  (
    SELECT
      connamespace,
      conrelid, conrelid::regclass::text AS conrel, unnest(conkey) AS conkey,
      confrelid, confrelid::regclass::text AS confrel, unnest(confkey) AS confkey
    FROM pg_catalog.pg_constraint r
    WHERE r.contype = 'f'
  ) con
  INNER JOIN pg_attribute con_attr ON con_attr.attrelid = con.conrelid AND con_attr.attnum = con.conkey
  INNER JOIN pg_namespace con_ns ON con_ns.oid = con.connamespace
  INNER JOIN (
    SELECT indrelid, indkey
    FROM pg_index
    WHERE indisprimary
  ) con_idx_pk ON con.confrelid = con_idx_pk.indrelid AND con.confkey = ANY(con_idx_pk.indkey)
) ON col.table_schema = con_ns.nspname AND col.table_name = con.conrel AND col.column_name = con_attr.attname
WHERE
  col.table_schema = 'public' AND
  col.column_name ~ '_id$' AND
  col_type.column_name IS NULL AND
  con.conkey IS NULL
ORDER BY col.table_name, col.column_name
;