List foreign keys with columns in PostgreSQL database

Query below returns foreign key constrant columns defined in a database.


select kcu.table_schema || '.' || kcu.table_name as foreign_table,
       '>-' as rel,
       rel_kcu.table_schema || '.' || rel_kcu.table_name as primary_table,
       kcu.ordinal_position as no,
       kcu.column_name as fk_column,
       '=' as join,
       rel_kcu.column_name as pk_column,
from information_schema.table_constraints tco
join information_schema.key_column_usage kcu
          on tco.constraint_schema = kcu.constraint_schema
          and tco.constraint_name = kcu.constraint_name
join information_schema.referential_constraints rco
          on tco.constraint_schema = rco.constraint_schema
          and tco.constraint_name = rco.constraint_name
join information_schema.key_column_usage rel_kcu
          on rco.unique_constraint_schema = rel_kcu.constraint_schema
          and rco.unique_constraint_name = rel_kcu.constraint_name
          and kcu.ordinal_position = rel_kcu.ordinal_position
where tco.constraint_type = 'FOREIGN KEY'
order by kcu.table_schema,


  • foreign_table - foreign table schema and name
  • rel - relationship symbol implicating direction
  • primary_table - primary (referenced) table schema and name
  • no - position of the column in key. Single coumn keys always have 1, composite keys have 1, 2, ... n for each column of the key
  • fk_column - foreign table column
  • join - "=" symbol indicating join operation for pair of columns
  • pk_column - primary (referenced) table column
  • constraint_name - foreign key constraint name


  • One row represents one foreign key column. If foreign key consists of multiple columns (composite key), each column appears separately.
  • Scope of rows: all foregin keys in a database and their columns
  • Ordered by foreign table schema name, table name and position

Sample results

Foreign keys in pagila database with their columns:

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.