PostgreSQL で配列比較する SQL文

テーブルのテキストフィールドでカンマ区切りで、IDを管理するスキーマがあります。
そのテーブルのデータは履歴管理されていて、直前のデータと最新のデータで、カンマ区切りのフィールドが変わったかどうか、SQLで比較したいという事がありました。

しかも、カンマ区切りの順序はバラバラで一意ではないので、ID順に並べ替えて配列で比較する必要がありました。

具体的な例だと

予約履歴テーブルとオプションテーブルがあり、予約履歴テーブルは更新される毎にデータがinsertされ、
optionというフィールドに、オプションテーブルのIDをカンマ区切りで保持しているというケースです。

string_to_arrayでカンマ区切りを配列にする

まずは、optionフィールドをstring_to_arrayでint[]型に変換します。

string_to_array(option ',')::int[]

array_sort_uniqueでソート＆ユニークにする

array_sort_unique という関数を定義して、重複する値を取り除き、ソートします。

CREATE OR REPLACE FUNCTION array_sort_unique (ANYARRAY) RETURNS ANYARRAY
LANGUAGE SQL
AS $body$
  SELECT ARRAY(
    SELECT DISTINCT $1[s.i]
    FROM generate_series(array_lower($1,1), array_upper($1,1)) AS s(i)
    ORDER BY 1
  );
$body$;
↓
SELECT array_sort_unique('{1,2,3,2,1}'::int[]);
 array_sort_unique 
-------------------
 {1,2,3}
(1 row)

COALESCEでnullをからの配列に置き換える

COALESCE(option, '{}'::int[])

全て合わせる

COALESCE(array_sort_unique(string_to_array(a.option, ',')::int[]), '{}'::int[]) = COALESCE(array_sort_unique(string_to_array(b.option, ',')::int[]), '{}'::int[])

そもそも、規則正しくoptionが保存されるという保証があればこのような手間は無かったのですが。。。