PostgreSQL　配列／行集合／CSV切替

配列の扱い

PostgreSQLはOracleに比べ配列の扱いがとても簡単で便利です。

配列の扱い

select array['みかん','りんご','ぶどう'] hairetsu;

配列の要素を縦出力したい場合はunnest()関数

select unnest(array['みかん','りんご','ぶどう'] );

配列の要素をCSV出力したい場合はarray_to_string()関数

select unnest(array['みかん','りんご','ぶどう'] );

まずは簡単な実行例

-- 配列
myposdb=# select array['みかん','りんご','ぶどう'] hairetsu;
        hairetsu
------------------------
 {みかん,りんご,ぶどう}
(1 行)

-- 配列をunnest()で縦出力（行集合）
myposdb=# select unnest(array['みかん','りんご','ぶどう'] );
 unnest
--------
 みかん
 りんご
 ぶどう
(3 行)

-- 配列をarray_to_string()を使ってCSVへ
myposdb=# select array_to_string(array['みかん','りんご','ぶどう'] ,',');
   array_to_string
----------------------
 みかん,りんご,ぶどう
(1 行)

配列に対する検索

次に実際に配列型のカラムを持つテーブルを作成して検索をします

-- 具体的にテーブル項目に配列を定義する
myposdb=# create table fruits (lno int,names varchar[])
myposdb-# ;
CREATE TABLE

-- テーブルへinsert
myposdb=# insert into fruits (lno,names) values
myposdb-#  (1,array['みかん','りんご','ぶどう'])
myposdb-# ,(2,array['ぶどう','みかん','りんご'])
myposdb-# ,(3,array['りんご','ぶどう','ぶどう'])
myposdb-# ,(4,array['葡萄','ぶどう'])
myposdb-# ,(5,array['ぶどう'])
myposdb-# ;
INSERT 0 5
myposdb=#

-- insertデータの確認
myposdb=# select * from fruits order by lno
myposdb-# ;
 lno |         names
-----+------------------------
   1 | {みかん,りんご,ぶどう}
   2 | {ぶどう,みかん,りんご}
   3 | {りんご,ぶどう,ぶどう}
   4 | {葡萄,ぶどう}
   5 | {ぶどう}
(5 行)

-- 配列の要素毎に分割出力
myposdb=# select lno,names[1],names[2],names[3] from fruits;
 lno | names  | names  | names
-----+--------+--------+--------
   1 | みかん | りんご | ぶどう
   2 | ぶどう | みかん | りんご
   3 | りんご | ぶどう | ぶどう
   4 | 葡萄   | ぶどう |
   5 | ぶどう |        |
(5 行)


-- 配列の１番目要素に対する条件検索
myposdb=# select * from fruits f where names[1]='ぶどう';
 lno |         names
-----+------------------------
   2 | {ぶどう,みかん,りんご}
   5 | {ぶどう}
(2 行)

-- 配列内全ての要素に対する条件検索
-- 配列内要素を全て１つの文字列にしたものに対する検索となります
myposdb=# select * from fruits
myposdb-# where array_to_string(names,'') like '%ぶどう%'
myposdb-# ;
 lno |         names
-----+------------------------
   1 | {みかん,りんご,ぶどう}
   2 | {ぶどう,みかん,りんご}
   3 | {りんご,ぶどう,ぶどう}
   4 | {葡萄,ぶどう}
   5 | {ぶどう}
(5 行)

-- unnest()関数を使って配列内要素を全て行集合にする事で
-- like 検索でなく等価比較する事ができます。
--
myposdb=# select x.lno,x.names from (
myposdb(# select f.lno,f.names,unnest(f.names) as name from fruits f
myposdb(# group by f.lno,f.names,unnest(f.names)
myposdb(# ) x where x.name ='ぶどう'
myposdb-# order by 1
myposdb-# ;
 lno |         names
-----+------------------------
   1 | {みかん,りんご,ぶどう}
   2 | {ぶどう,みかん,りんご}
   3 | {りんご,ぶどう,ぶどう}
   4 | {葡萄,ぶどう}
   5 | {ぶどう}
(5 行)


myposdb=#