Menu

PostgreSQL 配列の使い方:ANY・包含 (@>)・unnest

PostgreSQL の配列型の列を解説します。宣言のしかた、1 から始まる添字、ANY、@>、&& で値が含まれるか調べる方法、unnest、よく使う配列関数まで押さえます。

このページのコードはエディタで実行できます。編集してすぐに結果を確認できます。

PostgreSQL ではどの型も配列にできます。列を text[] や int[] で宣言し、値は '{a,b}' または ARRAY['a', 'b'] と書き、要素は tags[1] で読みます (添字は 1 から始まります)。配列に値が含まれるか調べるには 'value' = ANY(tags) か tags @> ARRAY['value'] を使います。

タグ sql を持つのは投稿 1 と 2 です。tags[1] は最初の要素で、cardinality は要素の数を数えます。2つのリテラルの書き方はどちらも同じ値を保存します。要素が式から来る場合やカンマを含む場合は ARRAY[...] のほうが書きやすいです。

配列に値が含まれるか調べる

要素が含まれるかの判定には4つの演算子を使います。

判定意味'{sql,json}' の場合
'sql' = ANY(tags)1つの値が配列に含まれる真
tags @> '{sql,json}'配列がこれらをすべて含む真
tags <@ '{sql,json,go}'すべての要素がこのリストに含まれる真
tags && '{go,json}'2つの配列に共通の要素が1つ以上ある真

両方のタグを持つのは投稿 1 だけです。投稿 2 と 3 は {go,json} と共通の要素を持ちます。no_go が false になるのは投稿 3 だけです。ALL は ANY の対になるもので、すべての要素について比較が成り立つ必要があります。

= ANY は SQL の NULL のルールに従います。値が見つからず、配列に NULL が含まれていると、結果は false ではなく NULL になります。そのため WHERE NOT ('x' = ANY(tags)) では、残るはずの行が消えることがあります。@> と && は true か false を返します。

大きなテーブルでは、GIN インデックスで @>、<@、&&、= が速くなります。= ANY(column) には効かないので、インデックスを使って探すときは tags @> ARRAY['sql'] と書きます。

CREATE INDEX posts_tags_idx ON posts USING gin (tags);

unnest で配列を行に展開する

unnest は要素ごとに1行を返すので、個々の要素で数えたり、結合したり、絞り込んだりできます。

sql は2つの投稿に、ほかのタグは1つずつに現れます。WITH ORDINALITY を付ける (unnest(tags) WITH ORDINALITY AS t(tag, pos)) と、各要素の位置も取得できます。行を配列にまとめ直すには array_agg を使います (string_agg と array_agg を参照)。

要素の追加、削除、変更

結果は {1,2,3}、{1,2,3,4}、{2,3}、{1,9,3}、2、{20,30}、a, b, c です。UPDATE で同じ関数を使えば保存済みの配列を変更できます: SET tags = array_append(tags, 'new') や SET tags = array_remove(tags, 'old')。array_remove はその値をすべて削除します。string_to_array('a,b,c', ',') はテキストを分割して配列にします。

配列の長さを取得する

cardinality(tags) は要素の数を返し、空の配列なら 0 です。array_length(tags, 1) は1次元目の長さを返し、空の配列なら NULL です。そのため WHERE array_length(tags, 1) = 0 は決して一致しません。空の配列かどうかは cardinality(tags) = 0 か tags = '{}' で調べます。

PostgreSQL の配列は多次元 (int[][]) にもできますが、内側の配列はすべて同じ長さでなければならず、宣言したサイズ (int[3]) は強制されません。入れ子のデータや長さがそろわないデータには、たいてい JSONB のほうが向いています。

よくある質問

PostgreSQL の配列の添字は 0 から始まりますか、1 から始まりますか?

デフォルトでは 1 から始まります。tags[1] が最初の要素です。末尾を超えた添字はエラーにならず NULL を返します。

PostgreSQL の IN と = ANY の違いは何ですか?

x IN (1, 2, 3) はクエリに書いたリストを受け取り、x = ANY(array) は1つの配列値を受け取ります。プレースホルダーのリストを組み立てる代わりに配列のパラメーターを1つ渡せる (WHERE id = ANY($1)) ので、アプリケーションのコードには ANY が向いています。

配列の列と別テーブル、どちらを使うべきですか?

タグのように、行と一緒に読み書きする短いリストには配列が向いています。要素ごとに列や外部キーが必要な場合や、要素を1つずつ更新する場合は、要素ごとに1行を持つ別テーブルのほうが検索も制約もしやすくなります。

PostgreSQL で配列が空か調べるには?

cardinality(arr) = 0 か arr = '{}' を使います。array_length は空の配列に NULL を返すので、array_length(arr, 1) = 0 は決して一致しません。

Coddyのプログラミング言語のイラスト

Coddyでコードを学ぼう

始める