PostgreSQL 複合プライマリキーのカラムをカンマ区切りで取得する

2017/6/24 PostgreSQL

タイトルそのままだが、全てのプライマリキーのカラムをプライマリーキー毎にカンマ区切りで取得する必要性が生じて、ここを参考にプライマリーキーのカラム一覧を取得する事までは出来た。

http://log.nissuk.info/2014/03/blog-post.html

これを縦横変換してカンマ区切りが取得できるかなと思ったが、対象は文字列なのでどうすっかなと調べたところ、ポスグレには string_agg なる便利な文字列の集約関数を発見！

Oracle にも ListAgg という文字列集約関数があるけどこちらより使いやすそう。

以下は全プライマリキーのカラムをカンマ区切りで取得するSQLです。

select
  b.table_name
  , b.constraint_name
  , string_agg(b.column_name, ',' order by b.ordinal_position) as cols 
from
  ( 
    SELECT
      c.ordinal_position
      , c.table_name
      , ccu.constraint_name
      , c.column_name
      , c.data_type 
    FROM
      information_schema.columns c 
      INNER JOIN information_schema.constraint_column_usage ccu 
        ON c.table_name = ccu.table_name 
        AND c.column_name = ccu.column_name 
      INNER JOIN information_schema.table_constraints tc 
        ON tc.table_catalog = c.table_catalog 
        AND tc.table_schema = c.table_schema 
        AND tc.table_name = c.table_name 
        AND tc.constraint_name = ccu.constraint_name 
    WHERE
      tc.constraint_type = 'PRIMARY KEY' 
    ORDER BY
      c.table_name
      , c.ordinal_position
  ) d 
group by
  table_name
  , constraint_name
;

なお string_agg はver9.0以上で利用可能。

photo by Travis Warren