tonextone.com/note/

「PostgreSQL で RDB 設計 その1」の補足。
PostgreSQL では、WHERE 句に、正規表現が使えるので、いつも重宝している。
こんな感じ。

• 使用前:

  select * from some_table where some_field = 1 or some_field = 4 or ... ;
  

• 使用後:

  select * from some_table where some_field ~ '^(1|4|...)$';
  

で、配列型のフィールド 'some_array' に対しても、
ANY(some_array) をカマせば、これができるだろうと期待していた。

いろいろ試したが、どうも無理っぽい[マニュアルより ANY/SOME(array)]。

そういう目的では、
配列型ではなく TEXT 型とかに配列表現を突っ込むのが良いだろう。
とりあえず TEXT 型で用意しておいて、記法は充分検討してゆっくり決めれば良い。

例えば、PHP の serialize(); の場合、数値配列は、

a:2:{i:0;i:123;i:1;i:456;}

という感じで、キーと値とが交互に表れるが、
このキーと値との選り分けを正規表現なんかでやってられない。
そうなると独自記法がベストな気がしてきた( JSON とか XML とかも無意味に面倒)。

正規表現のメタキャラクタとして解釈されなくて、
尚且つ、データに使われなさそうな文字列を、セパレータにすれば良い。
クォーテーションの扱いにも注意。

以下、例:

create table fruit
(
fruit_id     serial,
name         text
);

create table member
(
member_id    serial,
name         text,
gender       text,
fruit        text
);

insert into fruit(name) values('りんご');
insert into fruit(name) values('みかん');
insert into fruit(name) values('いちご');
insert into fruit(fruit_id,name) values(12,'いちじく');

insert into
member(name,gender,fruit)
values('太郎','男',':::::1:::::3:::::');

insert into
member(name,gender,fruit)
values('花子','女',':::::2:::::3:::::');

insert into
member(name,gender,fruit)
values('次郎','男',':::::12:::::');

select * from member,fruit
where member.fruit ~ (select ':{5}'||fruit.fruit_id||':{5}')
order by member_id;

 member_id | name | gender |      fruit        | fruit_id |   name
-----------+------+--------+-------------------+----------+----------
         1 | 太郎 | 男     | :::::1:::::3::::: |        1 | りんご
         1 | 太郎 | 男     | :::::1:::::3::::: |        3 | いちご
         2 | 花子 | 女     | :::::2:::::3::::: |        2 | みかん
         2 | 花子 | 女     | :::::2:::::3::::: |        3 | いちご
         3 | 次郎 | 男     | :::::12:::::      |       12 | いちじく
(5 rows)
Time: 1.069 ms

select * from member
where fruit ~ ':{5}(2|3):{5}'
order by member_id;

 member_id | name | gender |      fruit
-----------+------+--------+-------------------
         1 | 太郎 | 男     | :::::1:::::3:::::
         2 | 花子 | 女     | :::::2:::::3:::::
(2 rows)

Time: 0.557 ms