1

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.

Community
  • 1
  • 1
Biggie Mac
  • 1,307
  • 2
  • 13
  • 26
  • Looks like my code. You should include [a link to the question you got that from](http://stackoverflow.com/questions/15618312/find-the-referenced-table-name-using-table-field-and-schema-name). Also please add your version of Postgres – Erwin Brandstetter Aug 22 '13 at 16:17
  • Edited description and added link. Thanks – Biggie Mac Aug 23 '13 at 07:06

1 Answers1

2

This query adds the referenced column(s) for the foreign key constraint:

SELECT c.confrelid::regclass::text AS referenced_table
      ,string_agg(f.attname, ', ') AS referenced_columns
      ,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)
JOIN   pg_attribute  f ON f.attrelid = c.confrelid
                      AND f.attnum = ANY (confkey)
WHERE  a.attrelid = '"Schema"."Users"'::regclass   -- table name
AND    a.attname  = 'org_id'                       -- column name  
AND    c.contype  = 'f'
GROUP  BY c.confrelid, c.conname, c.oid;

A fk constraint can reference multiple columns. That's the reason for the aggregate function string_agg() in the query.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228