List foreign keys with columns in PostgreSQL database

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

Query

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,
       kcu.constraint_name
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,
         kcu.table_name,
         kcu.ordinal_position;

Columns

  • 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

Rows

  • 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:

0
There are no comments. Click here to write the first comment.