読者です 読者をやめる 読者になる 読者になる

Webエンジニア susumuis の技術ブログ

このブログの内容は個人の見解であり、所属する組織の公式見解ではありません

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 行)

つづく