I have a table Users with a field called org_id
which is a foreign key to a table organisation
, with primary key field organisation_id
. Knowing the table name (users) and the field name (users.org_id
), is there a query that can tell me the name and field that org_id
references?
I've found a Stackoverflow post similar to this where a query was provided to determine the referenced table name, but I also need to know the field name that is referenced:
SELECT c.confrelid::regclass::text AS referenced_table
,c.conname AS fk_name
,pg_get_constraintdef(c.oid) AS fk_definition
FROM pg_attribute a
JOIN pg_constraint c ON (c.conrelid, c.conkey[1]) = (a.attrelid, a.attnum)
WHERE a.attrelid = '"Schema"."Users"'::regclass -- table name
AND a.attname = 'org_id' -- column name
AND c.contype = 'f'
ORDER BY conrelid::regclass::text, contype DESC;
So the above query would return the name of the table (organisation
), the fk name and fk definition. Is there a way to also get the name of the field that is referenced? I know I could probably perform another query to determine the name of pk given a table but I would like to avoid performing multiple queries for this.