POSTGRES-SQL FIND FK with Referencing columns with different data type lengths

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.

Leave a Reply

Discover more from SQLYARD

Subscribe now to keep reading and get access to the full archive.

Continue reading