ポストグレスで配列を使うには？

/ PostgreSQL / By
ショコラ
ショコラ

ポストグレスで配列を使うには？

ポストグレスの配列についてのまとめ。

もっさん先輩
もっさん先輩

配列を表現するには２つの方法がある。

シングルクォートで波括弧（ブレース）を囲んで配列を表現する方法

SELECT '{1,2,3}';
postgres=# SELECT '{1,2,3}';
 ?column?
----------
 {1,2,3}

array と角括弧（ブラケット）を使って配列を表現する方法

SELECT array[1,2,3];
postgres=# SELECT array[1,2,3];
  array
---------
 {1,2,3}
(1 行)

テーブルを作成する。

CREATE TABLE num (
  id TEXT PRIMARY KEY,
  tags INTEGER[]
);

配列にインデックスを付ける。

CREATE INDEX idx_num_tags ON num USING GIN(tags);

配列をインサートする。

INSERT INTO num(id,tags) VALUES('001','{2,3,5,7,11,13,17,19}');

配列をインサートする。array も使えます。

INSERT INTO num(id,tags) VALUES('001',array[2,3,5,7,11,13,17,19]);

配列をアップデートする。

UPDATE num SET tags='{2,3,5,7,11,13,17,19,23,29,31,37,41,43,47}' WHERE id='001';

配列をアップデートする。array も使えます。

UPDATE num SET tags=array[2,3,5,7,11,13,17,19,23,29,31,37,41,43,47] WHERE id='001';

１つの値（19を含むもの）で配列を検索する。

SELECT * FROM num WHERE tags @> array[19];
postgres=# SELECT * FROM num WHERE tags @> array[19];
 id  |                    tags
-----+--------------------------------------------
 001 | {2,3,5,7,11,13,17,19,23,29,31,37,41,43,47}
(1 行)

複数の値（2と47を含むもの＝AND）で配列を検索する。

SELECT * FROM num WHERE tags @> array[2,47];
postgres=# SELECT * FROM num WHERE tags @> array[2,47];
 id  |                    tags
-----+--------------------------------------------
 001 | {2,3,5,7,11,13,17,19,23,29,31,37,41,43,47}
(1 行)

複数の値（1または19を含むもの＝OR）で配列を検索する。

SELECT * FROM num WHERE tags && array[1,19];
postgres=# SELECT * FROM num WHERE tags && array[1,19];
 id  |                    tags
-----+--------------------------------------------
 001 | {2,3,5,7,11,13,17,19,23,29,31,37,41,43,47}
(1 行)

副問い合わせで得た配列で配列を検索する。

SELECT * FROM num WHERE tags && (SELECT array[19]);
postgres=# SELECT * FROM num WHERE tags && (SELECT array[19]);
 id  |                    tags
-----+--------------------------------------------
 001 | {2,3,5,7,11,13,17,19,23,29,31,37,41,43,47}
(1 行)

ポイントは SELECT をただの丸括弧で囲むところです。

スカラーのカラム（？）を配列で検索する。ようするに「IN」。

SELECT * FROM num WHERE id = any(array['000','001','002']);
postgres=# SELECT * FROM num WHERE id = any(array['000','001','002']);
 id  |                    tags
-----+--------------------------------------------
 001 | {2,3,5,7,11,13,17,19,23,29,31,37,41,43,47}
(1 行)

UNNEST関数で配列を行に展開する。

SELECT id,unnest(tags) FROM num;
postgres=# SELECT id,unnest(tags) FROM num;
 id  | unnest
-----+--------
 001 |      2
 001 |      3
 001 |      5
 001 |      7
 001 |     11
 001 |     13
 001 |     17
 001 |     19
 001 |     23
 001 |     29
 001 |     31
 001 |     37
 001 |     41
 001 |     43
 001 |     47
(15 行)

UNNEST関数を使って join して取得する。

SELECT * FROM tag WHERE tag_id IN (SELECT unnest(tags::int[]) FROM num);

ARRAY_AGG関数を使って行から配列に変換する。ようするにUNNESTの逆。

SELECT array_agg(tag) FROM (SELECT unnest(tags) tag FROM num) tmp;
postgres=# SELECT array_agg(tag) FROM (SELECT UNNEST(tags) tag FROM num) tmp;
                                array_agg
--------------------------------------------------------------------------
 {2,3,5,7,11,13,17,19,23,29,31,37,41,43,47,21,23,29,31,33,37,47,51,53,59}
(1 行)

ARRAY_AGG関数を使って行から配列に変換する。DISTINCT で値を１つにする。

SELECT array_agg(DISTINCT tag) FROM (SELECT unnest(tags) tag FROM num) tmp;
postgres=# SELECT array_agg(DISTINCT tag) FROM (SELECT unnest(tags) tag FROM num) tmp;
                         array_agg
-----------------------------------------------------------
 {2,3,5,7,11,13,17,19,21,23,29,31,33,37,41,43,47,51,53,59}
(1 行)

配列に１つの値を追加する。

select array_append('{1,2}',3);
postgres=# select array_append('{1,2}',3);
 array_append
--------------
 {1,2,3}
(1 行)

配列に配列を追加する。

select array_cat('{1,2}','{2,3,4}');
postgres=# select array_cat('{1,2}','{2,3,4}');
  array_cat
-------------
 {1,2,2,3,4}
(1 行)
Scroll to Top