0

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?

2

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.