PostgresSQLでカンマ区切りのデータをwhere で絞り込みたい

PostgreSQL

PostgreSQL でカンマ区切りのデータを検索したい。そんなことがあると思います。ただこれを見てる方は必ず心の中で思っているでしょう。設計ミスじゃね？って。

そんなことをいまいってる場合じゃない。別テーブルにするなんてめんどくさい。という方のための記事です。実際自分はそんな感じでこなしました。。

  1. LIKE で検索をかけちゃダメ
  2. regexp_split_to_table
  3. パフォーマンスのいい方
  4. 検索したい
  5. 別テーブルに分けましょう
    1. ハマった点

LIKE で検索をかけちゃダメ

カンマ区切りのデータでどう考えてもかぶることがないというデータであれば問題はないかもしれません。そんな状態はあまりなさそうですけど。。
例えば以下のようなデータがあるときに

id|data
1 | 1,10,100,1000
2 | 11111
3 | 11,1100

このデータでLIKE で “1” を検索すると全部出てきます。文字列での部分一致なのであたりまえですけども。
というわけでカンマ区切りのデータを一時的にレコードに分けてあげる必要があります。
やり方は2つあります

regexp_split_to_table

検索するとよくでてくるのは regexp_split_to_table ですね。ひとまずこれを試してみます。

select id, regexp_split_to_table(カラム名, ',')  from comma_data;

id|data
1 | 1
1 | 10
1 | 100
1 | 1000
2 | 11111
3 | 11
3 | 1100

期待通りの結果が返ってきていますね。

パフォーマンスのいい方

もちろん上記のやり方でも全く問題ありません。もう一つの方法は、ちょっと回りくどいやり方ですが文字列から配列にして、配列からレコードをつくるやりかたです。こんな感じです。

select id, unnest(regexp_split_to_array(カラム名, ',')) from comma_data;

id|data
1 | 1
1 | 10
1 | 100
1 | 1000
2 | 11111
3 | 11
3 | 1100

返ってくるデータは変わらないです。

ただ自分で計測した限りはこっちの方が早かったのでこちらを採用しました。
海外のサイトですが、計測した結果 unnest の方がパフォーマンスはいいと書かれているので、ちょっとかっこわるいですがこっちのほうがいいみたいです。というよりもテーブル分けることが一番いいはずです。。
REGEXP_SPLIT_TO_TABLE AND STRING_TO_ARRAY UNNEST PERFORMANCE

検索したい

ただしこの状態では WHERE での検索ができないため、サブクエリにしてあげる必要があります。というわけで上記のものをサブクエリにしてあげましょう。

select * from (select id, unnest(regexp_split_to_array(カラム名, ',')) as data 
from comma_data) as c where c.カラム名 = '1';

id|data
1 | 1

select * from (select id, unnest(regexp_split_to_array(カラム名, ',')) as data 
from comma_data) as c where c.カラム名 in ('10','100');

id|data
1 | 10
1 | 100

問題なさそうです。ちなみに余計な処理なのでやっぱりパフォーマンスはよくないです。

別テーブルに分けましょう

結局カンマ区切りのデータだとパフォーマンスが悪いわけで、いつかは変えなきゃいけないわけだし。。というわけでさっさとテーブルを分けましょう。

insert into comma_insert ("id", "data")
select id, unnest(regexp_split_to_array(カラム名, ',')) as data from comma_data;

id|data
1 | 1
1 | 10
1 | 100
1 | 1000
2 | 11111
3 | 11
3 | 1100

というわけで、問題なく別テーブルにすることができました。これからは JOIN してあげればよくなりますね。一応動作を確認しましたが AS はいらなかったので、お好みではずしてください。

ハマった点

ただ一点自分がしばらくハマったことがあります。。それは普通の INSERT とはちょっと違かったことです。。

$ insert into comma_insert ("id", "data") values 
  select id, unnest(regexp_split_to_array(カラム名, ',')) as data from comma_data;
    ERROR:  syntax error at or near "select"
    LINE 2: select "id", "data" from comma_test;
            ^

一目瞭然！とは自分は思わずに、絶対あってるのに！という気持ちで愚直に VALUES 以外の部分で間違いがないかを探していました。。
というわけでテーブルの結果を別のテーブルにいれるときに values はいれちゃだめということで勉強になりました。。むしろこれが一番の収穫な気がするという。。

コメント

ホーム
トップ