【PostgreSQL】配列型の操作

PostgreSQLの配列型に対する操作がわからなかったので調べた

pgsql-jp.github.io

配列型の定義と使い方

配列型の定義

PostgreSQLの配列型は、任意のデータ型に対して配列を作成できる

配列型はデータ型[]のように記述する

たとえば、整数の配列を作りたい場合は以下のようにする

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    favorite_numbers INTEGER[],
    tags TEXT[]
);

この例では、favorite_numbersカラムは整数の配列を、tagsカラムは文字列の配列を格納できる

配列の挿入

配列型のカラムにデータには以下のように挿入できる

INSERT INTO users (favorite_numbers, tags) 
VALUES ('{3, 7, 9}', '{"tag1", "tag2", "tag3"}');

多次元配列の挿入

テーブルの列を可変長多次元配列としても定義できる

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

挿入は以下のように行う

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

配列演算子

配列内の要素が存在するかをチェックする (= ANY)

配列に特定の値が含まれているかどうかをチェックするには、= ANYやINを使う

SELECT * FROM users WHERE 3 = ANY(favorite_numbers);

このクエリは、favorite_numbersに3が含まれる行を取得する

配列の長さを取得する (array_length)

配列の長さを取得するには、array_length関数を使う

SELECT array_length(favorite_numbers, 1) FROM users;

array_lengthの第2引数には、配列の次元を指定する

多次元配列の場合は、2以上を指定して次元ごとの長さを取得できる

配列同士の包含関係をチェックする (@>, <@)

• @>: 左の配列が右の配列を含む場合にtrueを返す
• <@: 左の配列が右の配列に含まれる場合にtrueを返す

SELECT * FROM users WHERE favorite_numbers @> ARRAY[3];

このクエリは、favorite_numbersが3を含む行を取得する

配列の結合 (||)

配列同士を結合するには、||を使う

SELECT favorite_numbers || ARRAY[10, 11] FROM users;

このクエリは、favorite_numbersの配列に10と11を追加した配列を返す

配列のスライス ([start:end])

特定の範囲の要素を取得するには、スライス記法を使う

SELECT favorite_numbers[1:2] FROM users;

このクエリは、favorite_numbersの1番目から2番目の要素を取得する

配列関数

array_append

配列の最後に要素を追加

SELECT array_append(favorite_numbers, 4) FROM users;

このクエリは、favorite_numbersの末尾に4を追加した配列を返す

array_prepend

配列の最初に要素を追加

SELECT array_prepend(1, favorite_numbers) FROM users;

このクエリは、favorite_numbersの先頭に1を追加した配列を返す

unnest

配列を展開して、1つの要素ごとに行として返す

SELECT unnest(favorite_numbers) FROM users;

このクエリは、favorite_numbersの要素を1つずつ展開して表示する

array_to_string

配列を文字列に変換

区切り文字を指定することで、配列の要素を連結して1つの文字列にできる

SELECT array_to_string(tags, ', ') FROM users;

このクエリは、tagsの要素をカンマ区切りで連結した文字列を返す

string_to_array

文字列を特定の区切り文字で分割して配列に変換

SELECT string_to_array('tag1, tag2, tag3', ', ');

このクエリは、カンマ区切りの文字列を配列に変換