Find the referenced table name using table, field and schema name

Question

I have a requirement where I need to find the referenced table name (Primary key table name) by a particular field in a table (Foreign key table) using this field name, table name (where this field resides) and the schema name (where the table and thereby the field resides)

For example:

Schema1.TableA
  Id (Integer, PK)  
  Name varchar


Schema2.TableB
  Id (integer, PK)  
  A_Id (integer, FK referencing TableA.Id)  
  Name varchar

I need to pass A_Id, TableB and Schema2 to a function and get Schema1.TableA as result.

I am using Postgres 8.3.

I think you need to query the information_schema.key_column_usage table. That schema isn't in your path, so start with something like select * from information_schema.key_column_usage;. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Mar 25, 2013 at 15:33

Erwin Brandstetter · Accepted Answer · 2022-12-14 03:00:54Z

If you don't need this to be portable to another RDBMS (or another major Postgres version) it is faster and simpler to use the catalog tables in pg_catalog instead of the standard information schema:

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 = '"Schema2"."TableB"'::regclass   -- table name
AND    a.attname  = 'A_Id'                           -- column name  
AND    c.contype  = 'f'
ORDER  BY referenced_table, c.contype DESC;

Returns:

referenced_table	fk_name	fk_definition
Schema1.TableA	b1_fkey	FOREIGN KEY ("B_id") REFERENCES "Schema1"."TableA"("A_id")

You only asked for the first column. I added two columns for context.

This returns all referenced tables by all foreign keys involving the given column name - including FK constraints on multiple columns.

The name is automatically schema-qualified if necessary according to the current search_path. It is also double-quoted automatically where needed.

Check out details of pg_constraint and pg_attribute in the manual. More about object identifier types, too.

Collectives™ on Stack Overflow

Find the referenced table name using table, field and schema name

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related