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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Log in using
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;