Getting referenced tables in Postgres

Question

I have a list of foreign keys. I'd like to find out the tables where these FK's point to and the actual key the point to.

I've got a list of FK's like so:

columnName0, columnName1, columnName2

Foreign key references

• columnName0 references table0.idTable0
• columnName1 references table1.idTable1
• columnName2 references table2.idTable2

Some sample tables:

Table0:

idTable0, PK
name

Table1:

idTable1, PK
age

Table2:

idTable2, PK
createdOn

A sample result:

| column      | referenced_column | referenced_table |
|-------------|-------------------|------------------|
| columnName0 | idTable0          | table0           |
| columnName1 | idTable1          | table1           |
| columnName2 | idTable2          | table2           |

I'm trying to translate something I do in MySQL like this:

SELECT DISTINCT
    COLUMN_NAME AS column,
    REFERENCED_COLUMN_NAME AS referenced_column,
    REFERENCED_TABLE_NAME AS referenced_table
FROM 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE 
    COLUMN_NAME IN (?);

I'm going to have to use straight-up queries (unfortunately, no stored procedures).

Answer 1

You can query pg_constraint. For column names you should lookup pg_attribute. A foreign key may be based on multiple columns, so conkey and confkey of pg_constraint are arrays. You have to unnest the arrays to get a list of column names. Example:

select 
    conrelid::regclass table_name, 
    a1.attname column_name, 
    confrelid::regclass referenced_table, 
    a2.attname referenced_column,
    conname constraint_name
from (
    select conname, conrelid::regclass, confrelid::regclass, col, fcol
    from pg_constraint c, 
    lateral unnest(conkey) col, 
    lateral unnest(confkey) fcol
    where contype = 'f'   -- foreign keys constraints
    ) s
join pg_attribute a1 on a1.attrelid = conrelid and a1.attnum = col
join pg_attribute a2 on a2.attrelid = confrelid and a2.attnum = fcol;

 table_name | column_name | referenced_table | referenced_column |    constraint_name     
------------+-------------+------------------+-------------------+------------------------
 products   | image_id    | images           | id                | products_image_id_fkey
(1 row)

---

In Postgres 9.4 or later the function unnest() may have multiple arguments and the inner query may look like this:

...
    select conname, conrelid::regclass, confrelid::regclass, col, fcol
    from pg_constraint c, 
    lateral unnest(conkey, confkey) u(col, fcol)
    where contype = 'f'   -- foreign keys constraints
...