Search code examples
postgresqlforeign-keys

Get table name by foreign key

I have a two tables in the database. First table has a foreign key to second table.

Can I get second table name by the foreign key from first table?

Solution

  • Yes, you can.

    SELECT ct.oid, conname, condeferrable, condeferred, confupdtype, confdeltype, confmatchtype, conkey, confkey, confrelid, nl.nspname as fknsp, cl.relname as fktab, nr.nspname as refnsp, cr.relname as reftab, description, convalidated
      FROM pg_constraint ct
      JOIN pg_class cl ON cl.oid=conrelid
      JOIN pg_namespace nl ON nl.oid=cl.relnamespace
      JOIN pg_class cr ON cr.oid=confrelid
      JOIN pg_namespace nr ON nr.oid=cr.relnamespace
      LEFT OUTER JOIN pg_description des ON (des.objoid=ct.oid AND des.classoid='pg_constraint'::regclass)
     WHERE contype='f' AND ct.conname = 'yourfk_name' -- conrelid = 26941::oid
     ORDER BY conname;

    Just valorize yourfk_name with your foreign_key name. Or use oid if prefered.
    This works on 9.3 but I think also 9.x.