I often need to inspect fkey usage in Postgres to audit views , and it can be a PITA combing through data.
I found this little gem on the postgres mailing list by Thomas Kellerer
> SELECT c.table_name || ‘ references ‘ || tu.table_name
FROM information_schema.table_constraints c JOIN information_schema.constraint_table_usage tu ON (c.constraint_name = tu.constraint_name AND c.table_schema = tu.table_schema)
WHERE c.constraint_type = ‘FOREIGN KEY’ ;
That lists all your table fkey relations
With a little bit of magic we can clean it up to alphabetize a bit…
> order by c.table_name , tu.table_name
and if you want to find what tables are referencing the table you’re on…
> WHERE c.table_name = ‘$$NAME$$’ or tu.table_name = ‘$$NAME$$’
and to make things a bit more clear… we can add in the constraint name
> SELECT c.table_name || ‘ references ‘ || tu.table_name || ‘ via ‘ || tu.constraint_name
FROM information_schema.table_constraints c JOIN information_schema.constraint_table_usage tu ON (c.constraint_name = tu.constraint_name AND c.table_schema = tu.table_schema)
WHERE c.constraint_type = ‘FOREIGN KEY’ ORDER BY c.table_name, tu.table_name;
I’m not sure how the actual fkeys are stored in terms of columns, but if anyone has an idea, i’d love to update this call to list the columns on each table