SELECT
con.conname AS constraint_name,
ns1.nspname || '.' || rel1.relname AS referencing_table,
att2.attname AS referencing_column,
pg_catalog.format_type(att2.atttypid, att2.atttypmod) AS referencing_type,
att2.atttypmod AS referencing_typemod,
ns2.nspname || '.' || rel2.relname AS referenced_table,
att.attname AS referenced_column,
pg_catalog.format_type(att.atttypid, att.atttypmod) AS referenced_type,
att.atttypmod AS referenced_typemod
FROM
pg_constraint con
JOIN
pg_class rel1 ON rel1.oid = con.conrelid
JOIN
pg_namespace ns1 ON ns1.oid = rel1.relnamespace
JOIN
pg_class rel2 ON rel2.oid = con.confrelid
JOIN
pg_namespace ns2 ON ns2.oid = rel2.relnamespace
JOIN
pg_attribute att2 ON att2.attrelid = con.conrelid AND att2.attnum = ANY(con.conkey)
JOIN
pg_attribute att ON att.attrelid = con.confrelid AND att.attnum = ANY(con.confkey)
WHERE
con.contype = 'f'
AND att2.atttypid = att.atttypid
AND att2.atttypmod != att.atttypmod
ORDER BY
con.conname;
Discover more from SQLYARD
Subscribe to get the latest posts sent to your email.


