PostgreSQLでカンマ区切りでフラグを管理する

Webサービスの設定において、チェックボックスなどの入力値を保存することがよくあります。その際、解決策にもいくつかあります。

• 正規化して専用のテーブルを作成
• 1つの項目に2進数のフラグで保存
• 1つの項目にカンマ区切りで保存

今回は、PostgreSQL でカンマ区切りで保存した項目の検索方法を紹介します。

検証用テーブルの作成

まず、検証用のテーブルを作成します。

1CREATE TABLE search_comma
2(
3  id serial,
4  flags varchar(100),
5  CONSTRAINT search_comma_pkey PRIMARY KEY (id)
6);

次に検証用のデータを登録します。

1INSERT INTO search_comma
2  (flags)
3VALUES
4  ('1,2,4'),
5  ('1'),
6  ('3,4'),
7  ('5'),
8  ('2,3,6'),
9  ('5,2'),
10  ('55,22'),
11  ('3,12345')
12;  

登録されているデータは以下になります。

PostgreSQLでの検索方法

単純にLIKEで検索すると3,33を誤検出してしまったりしまうため、難しいですが、一度配列にして要素に該当の値があるかを検索することができます。

‘2’を含む行を検索するクエリ。「regexp_split_to_array」で指定の文字で配列に変換し、「@>」が包含の演算子になります。

1SELECT
2  *
3FROM
4  search_comma
5WHERE
6  regexp_split_to_array(flags, ',') @> ARRAY['2']
7;

結果は、期待どおりになります。

複数のフラグの場合は、両方を持つ場合と、どちらかを持つ場合で演算子が変えることをでどちらも対応することができます。

複数のフラグを両方持つ場合

先ほど記載した「@>」演算子になります。

1SELECT
2  *
3FROM
4  search_comma
5WHERE
6  regexp_split_to_array(flags, ',') @> ARRAY['2', '5']
7;

2と5の両方を持つ行の検索になり、結果は以下になります。

複数のフラグのどちらかを持つ場合

どちらかが含まれていれば良い場合は、「&&」演算子を使います。

1SELECT
2  *
3FROM
4  search_comma
5WHERE
6  regexp_split_to_array(flags, ',') && ARRAY['2', '5']
7;

2と5のどちらかを持つ行の検索になり、結果は以下になります。

そのほかの演算子

比較演算子は、紹介した2つで、あとは配列を結合する演算子があります。
PostgreSQLのドキュメントに記載されています。

https://www.postgresql.jp/docs/13/functions-array.html

まとめ

SQLの内容からも、全行を配列にして検索するので、インデックスを活用したクエリになっていません。
そのため、レコード数が膨大なテーブルに使用するのは向いておりません。
膨大なテーブルの場合は、素直にテーブルを分割した方がいいと思います。

私は、レコード数が多くならない管理系のテーブルによく使用しています。

使うシーン・データ量を検討したうえで、採用いただければと思います。

mabushiisign

Twitter

B'zとdoaとミスチルと登山が好きな40代のプログラマです。 普段は、SIer企業で働いています。