PostgreSQLの外部参照の情報を返すクエリ

調べるのに意外と時間がかかったのでメモしておきます。

テーブルresponsesの列requestがrequestsテーブルの列idを指しているとします。

CREATE TABLE requests (id serial PRIMARY KEY, message text);
CREATE TABLE responses (request int REFERENCES requests(id), message text);

#制約のタイプ
information_schema.table_constraintsに制約のタイプ「FOREIGN KEY」があります。

select constraint_name, constraint_type
   from information_schema.table_constraints;

 constraint_name    | constraint_type 

------------------------+-----------------
responses_request_fkey | FOREIGN KEY

#参照している列の情報
information_schema.key_column_usageに参照している列の情報があります。

select constraint_name, table_name, column_name
    from information_schema.key_column_usage;

 constraint_name    | table_name | column_name 

------------------------+------------+-------------
responses_request_fkey | responses | request

#参照されている列の情報
information_schema.constraint_column_usageに参照されている列の情報があります。

select constraint_name, table_name, column_name
    from information_schema.constraint_column_usage;

 constraint_name    | table_name | column_name 

------------------------+------------+-------------
responses_request_fkey | requests | id

#まとめ
これらの情報をまとめます。

select k.table_name, k.column_name, c.table_name, c.column_name
    from information_schema.table_constraints as t,
         information_schema.key_column_usage as k,
         information_schema.constraint_column_usage as c
    where t.constraint_type = 'FOREIGN KEY'
      AND t.constraint_name = k.constraint_name
      AND t.constraint_name = c.constraint_name;

table_name | column_name | table_name | column_name
------------+-------------+------------+-------------
responses | request | requests | id