PostgreSQLを使っていて、カラムの中にカンマ区切りデータを入れてしまった時のノウハウあれこれ

一つのカラムのなかに、カンマ区切りでデータを入れてしまったとします。自分用のメモとして、そんな時に役立つテクニックをここにまとめます。言ってみれば第一正規形ですらないテーブルデータと言えるので、いかがなものかとも思いますが、それはそれとして。

前提として、値に","は含まないこととし、エスケープとかは考えないことにします。

カンマ区切りを分離してテーブルとしてselectする（regexp_split_to_table）

select regexp_split_to_table('one,two,three', ',');

結果

regexp_split_to_table

• • • • • • • • • • • • • • • • • • • • • • -

one
two
three
(3 行)

参考：http://blog.cloudpack.jp/2011/10/server-news-postgres-comma-separate.html

複数行（1列）のテーブルを結合してカンマ区切りにする（array_to_string）

select c1 from t1

の結果が

c1

• • • • • • • • • • • • • • • • • • • • • • -

one
two
three
(3 行)

のとき、

select array_to_string(ARRAY(select c1 from t1), ',')

結果

array_to_string

• • • • • • • • • • • • • • • • -

one,two,three
(1 行)

参考：http://postgresql.g.hatena.ne.jp/iakio/20080427/1209276667

カンマ区切りの中にある要素が含まれているかどうかを調べる（',' || column_name || ',' like '%,' || keyword || ',%'）

一個目の方法でexistsとかやってもいいのですが、いかにも遅そう。そこで、この方法を使うと、インデックスこそ使われませんが程々高速にできる（と思っています）

select ',' || 'hoge,fuga,piyo' || ',' like '%,' || 'hoge' || ',%'

結果

?column?

• • • • • • • • • -

t
(1 行)

select ',' || 'fuga,piyo' || ',' like '%,' || 'hoge' || ',%'

結果

?column?

• • • • • • • • • -

f
(1 行)

つづく