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 は決して一致しません。