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 行)
つづく