月別アーカイブ: 2013年12月

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

参考: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 行)