PostgreSQL query that finds columns and referenced columns from a constraint
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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