PostgreSQL query that finds columns and referenced columns from a constraint
WITH constraint_ref_fields AS ( | |
SELECT | |
unnest(con1.conkey) AS local_key, | |
unnest(con1.confkey) AS ref_key, | |
con1.conrelid, | |
con1.confrelid | |
FROM | |
pg_class cl | |
JOIN pg_namespace ns ON cl.relnamespace = ns.oid | |
JOIN pg_constraint con1 ON con1.conrelid = cl.oid | |
WHERE | |
con1.oid = ? --constraint oid | |
) | |
SELECT | |
att.attname AS local_column, | |
att2.attname AS ref_column | |
FROM | |
constraint_ref_fields con | |
JOIN pg_attribute att ON att.attrelid = con.conrelid AND att.attnum = con.local_key | |
LEFT JOIN pg_attribute att2 ON att2.attrelid = con.confrelid AND att2.attnum = con.ref_key; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment