PostgreSQL – How to Get Table Name by Foreign Key

foreign keypostgresql

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?

Best Answer

Log in using psql, issue 

\d your_table

and look up the referenced table's name. If you set \set ECHO_HIDDEN on beforehand, you'll get a bunch of queries that produce the output, those you can reuse in your own discovery script.

For example, on my test database these look like

test=# \d tfk
       Table "test.tfk"
 Column │  Type   │ Modifiers 
────────┼─────────┼───────────
 t_id   │ integer │ 
Foreign-key constraints:
    "tfk_t_id_fkey" FOREIGN KEY (t_id) REFERENCES t(t_id)

The query you are interested in is:

SELECT conname,
  pg_catalog.pg_get_constraintdef(r.oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '32850' AND r.contype = 'f' ORDER BY 1;

With a minor change, you get the table name like 

SELECT confrelid::regclass AS foreign_table_name
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '32850' AND r.contype = 'f' ORDER BY 1;
Related Question