PostgreSQL 配列操作：SQLクエリとPython (psycopg2) 実践例

2025-05-27

配列の基本的な概念

インデックス
各要素はインデックス（添え字）によってアクセスできます。PostgreSQLの配列のインデックスはデフォルトで1から始まります。
順序付けられた要素
配列内の要素は特定の順序を持ちます。
同じデータ型
配列に格納できる値はすべて同じデータ型である必要があります。（例：整数の配列、文字列の配列など）

配列の作成と宣言

テーブルの作成時や、既存のテーブルにカラムを追加する際に、配列型を指定できます。

-- 整数の配列を格納するカラムを持つテーブルを作成する例
CREATE TABLE scores (
    id SERIAL PRIMARY KEY,
    student_name VARCHAR(100),
    test_scores INTEGER[]
);

-- 文字列の配列を格納するカラムを持つテーブルを追加する例
ALTER TABLE scores
ADD COLUMN hobbies TEXT[];

上記の例では、test_scores カラムは整数の配列 (INTEGER[]) を格納し、hobbies カラムは文字列の配列 (TEXT[]) を格納します。

配列へのデータの挿入

配列にデータを挿入するには、配列リテラルを使用します。配列リテラルは、波括弧 {} で囲み、要素をカンマ , で区切って記述します。

-- 整数の配列を挿入する例
INSERT INTO scores (student_name, test_scores)
VALUES ('田中太郎', '{85, 92, 78}');

-- 文字列の配列を挿入する例
INSERT INTO scores (student_name, hobbies)
VALUES ('山田花子', '{"読書", "料理", "旅行"}');

配列要素へのアクセス

配列の特定の要素にアクセスするには、カラム名の後に角括弧 [] でインデックスを指定します。

-- 最初のテストの点数を取得する
SELECT student_name, test_scores[1]
FROM scores
WHERE student_name = '田中太郎'; -- 結果: 85

-- 2番目の趣味を取得する
SELECT student_name, hobbies[2]
FROM scores
WHERE student_name = '山田花子'; -- 結果: 料理

配列全体へのアクセス

配列全体にアクセスするには、単にカラム名を指定します。

SELECT student_name, test_scores, hobbies
FROM scores;

配列の操作関数と演算子

PostgreSQLは、配列を操作するための多くの関数と演算子を提供しています。いくつか例を挙げます。

&& (オーバーラップ演算子): 2つの配列に共通の要素がある場合に真を返します。
<@ (被包含演算子): 左側の配列のすべての要素が右側の配列に含まれる場合に真を返します。
@> (包含演算子): 左側の配列が右側の配列のすべての要素を含む場合に真を返します。
|| (連結演算子): 2つの配列を連結します。
unnest(array): 配列を個別の行に展開します。
array_upper(array, dimension): 配列の指定された次元の上限インデックスを取得します。
array_lower(array, dimension): 配列の指定された次元の下限インデックスを取得します。
array_length(array, dimension): 配列の指定された次元の長さを取得します。

配列の利用例

ユーザーの複数の興味やスキル
ユーザーが持つ複数の興味やスキルを管理する場合。
時系列データの保存
一定期間の測定値やイベントを一つのレコードに保存する場合。
複数のタグやカテゴリの管理
一つの記事や製品に複数のタグやカテゴリを関連付ける場合。

配列内の要素に対する検索は、通常のインデックスが直接利用できないため、工夫が必要になる場合があります。（GINインデックスなどが有効な場合があります）
配列のサイズが非常に大きくなると、パフォーマンスに影響を与える可能性があります。

配列に関する一般的なエラー

- エラー例
  配列カラムが INTEGER[] 型であるにもかかわらず、文字列を挿入しようとする。
- エラーメッセージの例
  invalid input syntax for integer: "abc" (整数に対する無効な入力構文: "abc")
- 原因
  配列に格納しようとしている値のデータ型が、配列カラムの定義と一致しない。
- トラブルシューティング
  挿入または更新しようとしているデータの型を確認し、配列カラムの型と一致するように修正します。
インデックスの範囲外アクセス
- エラー例
  要素数が3の配列に対して、インデックス4を指定してアクセスしようとする。
- エラーメッセージの例
  array index out of bounds: 4 (配列のインデックスが範囲外です: 4)
- 原因
  配列の有効なインデックス範囲（デフォルトは1から配列の長さまで）を超えたインデックスを指定している。
- トラブルシューティング
  アクセスしようとしているインデックスが、配列の実際の要素数以内であることを確認します。array_length() 関数などで配列の長さを確認できます。
配列リテラルの構文エラー
- エラー例
  配列リテラルを {1, 2, 3 のように、閉じ括弧を忘れて記述する。
- エラーメッセージの例
  syntax error at or near "{" ("{ " の付近で構文エラー)
- 原因
  配列リテラルの記述形式 {要素1, 要素2, ...} が正しくない。
- トラブルシューティング
  配列リテラルが波括弧 {} で囲まれ、要素がカンマ , で正しく区切られているかを確認します。文字列要素の場合はダブルクォート " " で囲む必要がある場合もあります。
配列型のカラムに対する非配列の値を挿入しようとする
- エラー例
  INTEGER[] 型のカラムに、単一の整数値 10 を挿入しようとする。
- エラーメッセージの例
  明確なエラーメッセージが出ない場合もありますが、意図しない動作やデータ型の不整合が発生する可能性があります。
- 原因
  配列型のカラムには、配列リテラル {...} または配列を返す関数を使用する必要があります。
- トラブルシューティング
  単一の値を挿入したい場合は、配列リテラル {10} のように記述するか、カラムの型を配列型から適切な単一の型に変更することを検討します。
配列操作関数の誤用
- エラー例
  array_length() 関数に配列ではない値を渡す。
- エラーメッセージの例
  関数によって異なりますが、データ型の不一致に関するエラーが出ることがあります。
- 原因
  配列操作関数は、適切な配列型の引数を必要とします。
- トラブルシューティング
  使用している配列操作関数のドキュメントを確認し、引数の型が正しいかを確認します。

配列に関するトラブルシューティング

SQLクエリの例

まず、以下のような scores テーブルがあることを前提とします。

CREATE TABLE scores (
    id SERIAL PRIMARY KEY,
    student_name VARCHAR(100),
    test_scores INTEGER[],
    hobbies TEXT[]
);

INSERT INTO scores (student_name, test_scores, hobbies)
VALUES
    ('田中太郎', '{85, 92, 78}', '{"読書", "料理"}'),
    ('山田花子', '{90, 88, 95}', '{"旅行", "写真", "音楽"}'),
    ('佐藤健', '{75, 80, 82}', '{"ゲーム"}');

例1: 特定の生徒のテストの点数を取得する

SELECT student_name, test_scores
FROM scores
WHERE student_name = '田中太郎';
-- 結果: 田中太郎 | {85,92,78}

例2: 特定の生徒の最初のテストの点数を取得する

SELECT student_name, test_scores[1] AS first_score
FROM scores
WHERE student_name = '山田花子';
-- 結果: 山田花子 | 90

例3: 特定の生徒の2番目の趣味を取得する

SELECT student_name, hobbies[2] AS second_hobby
FROM scores
WHERE student_name = '山田花子';
-- 結果: 山田花子 | 写真

例4: 配列の長さを取得する

SELECT student_name, array_length(test_scores, 1) AS number_of_tests
FROM scores;
-- 結果:
-- 田中太郎 | 3
-- 山田花子 | 3
-- 佐藤健   | 3

例5: 配列に特定の要素が含まれているレコードを検索する (@> 演算子)

SELECT student_name, hobbies
FROM scores
WHERE hobbies @> '{"料理"}';
-- 結果: 田中太郎 | {"読書","料理"}

例6: 配列の要素を個別の行に展開する (unnest() 関数)

SELECT student_name, unnest(test_scores) AS score
FROM scores
WHERE student_name = '田中太郎';
-- 結果:
-- 田中太郎 | 85
-- 田中太郎 | 92
-- 田中太郎 | 78

SELECT student_name, unnest(hobbies) AS hobby
FROM scores
WHERE student_name = '山田花子';
-- 結果:
-- 山田花子 | 旅行
-- 山田花子 | 写真
-- 山田花子 | 音楽

例7: 新しい要素を配列に追加する (array_append() 関数)

UPDATE scores
SET test_scores = array_append(test_scores, 98)
WHERE student_name = '佐藤健';

SELECT student_name, test_scores
FROM scores
WHERE student_name = '佐藤健';
-- 結果: 佐藤健 | {75,80,82,98}

例8: 配列から特定の要素を削除する (array_remove() 関数)

UPDATE scores
SET hobbies = array_remove(hobbies, '読書')
WHERE student_name = '田中太郎';

SELECT student_name, hobbies
FROM scores
WHERE student_name = '田中太郎';
-- 結果: 田中太郎 | {"料理"}

Python (psycopg2) の例

import psycopg2

# データベースへの接続情報
db_config = {
    'host': 'localhost',
    'database': 'your_database',
    'user': 'your_user',
    'password': 'your_password'
}

try:
    # データベースに接続
    conn = psycopg2.connect(**db_config)
    cur = conn.cursor()

    # 例1: 生徒の名前とテストの点数を取得し、表示する
    cur.execute("SELECT student_name, test_scores FROM scores")
    results = cur.fetchall()
    for row in results:
        print(f"生徒名: {row[0]}, テストの点数: {row[1]}")

    print("-" * 20)

    # 例2: 特定の生徒の趣味を更新する
    new_hobbies = ['映画', '音楽']
    student_name_to_update = '田中太郎'
    cur.execute("UPDATE scores SET hobbies = %s WHERE student_name = %s", (new_hobbies, student_name_to_update))
    conn.commit()
    print(f"{student_name_to_update} の趣味を {new_hobbies} に更新しました。")

    cur.execute("SELECT student_name, hobbies FROM scores WHERE student_name = %s", (student_name_to_update,))
    updated_hobbies = cur.fetchone()
    print(f"{updated_hobbies[0]} の現在の趣味: {updated_hobbies[1]}")

    print("-" * 20)

    # 例3: 新しい生徒の情報を配列を含む形で挿入する
    new_student = ('鈴木一郎', [95, 98, 92], ['サッカー', 'テニス'])
    cur.execute("INSERT INTO scores (student_name, test_scores, hobbies) VALUES (%s, %s, %s)", new_student)
    conn.commit()
    print(f"{new_student[0]} の情報を追加しました。")

    cur.execute("SELECT student_name, test_scores, hobbies FROM scores WHERE student_name = %s", (new_student[0],))
    new_student_data = cur.fetchone()
    print(f"{new_student_data[0]} のデータ: テストの点数={new_student_data[1]}, 趣味={new_student_data[2]}")

except psycopg2.Error as e:
    print(f"データベースエラーが発生しました: {e}")

finally:
    # 接続を閉じる
    if conn:
        cur.close()
        conn.close()

接続
psycopg2.connect() 関数を使ってPostgreSQLデータベースに接続します。接続情報は辞書形式で渡します。
カーソル
conn.cursor() でカーソルオブジェクトを作成します。これを使ってSQLクエリを実行します。
SELECT
cur.execute() でSELECT文を実行し、cur.fetchall() で結果をfetchall() で取得します。配列はPythonのリストとして扱われます。
UPDATE
UPDATE 文で配列カラムを更新します。プレースホルダ %s を使用して、SQLインジェクションを防ぎます。Pythonのリストをそのままプレースホルダに渡すことができます。conn.commit() で変更を確定します。
INSERT
INSERT 文で新しいレコードを挿入します。配列データもPythonのリストとしてプレースホルダに渡します。

JSONまたはJSONB型を使用する

配列の代わりに、JSONまたはJSONB型のカラムを使用して、複数の値を格納することができます。JSONB型はバイナリ形式で格納され、JSON型よりも効率的な検索と操作が可能です。

SQLの例

-- JSONB型のカラムを持つテーブルを作成
CREATE TABLE users_jsonb (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    interests JSONB
);

-- JSONBデータを挿入
INSERT INTO users_jsonb (name, interests)
VALUES
    ('山田太郎', '["読書", "映画", "音楽"]'),
    ('佐藤花子', '{"sports": ["サッカー", "テニス"], "hobbies": ["料理", "旅行"]}');

-- JSONBデータから特定の要素を取り出す
SELECT name, interests ->> 0 AS first_interest
FROM users_jsonb
WHERE name = '山田太郎'; -- 結果: 山田太郎 | 読書

SELECT name, interests -> 'sports' ->> 1 AS second_sport
FROM users_jsonb
WHERE name = '佐藤花子'; -- 結果: 佐藤花子 | テニス

-- JSONBデータに特定の要素が含まれているレコードを検索
SELECT name, interests
FROM users_jsonb
WHERE interests @> '["映画"]'::jsonb; -- 結果: 山田太郎 | ["読書", "映画", "音楽"]

Python (psycopg2) の例

import psycopg2
import json

# ... (データベース接続設定は省略) ...

try:
    conn = psycopg2.connect(**db_config)
    cur = conn.cursor()

    # JSONBデータを挿入
    new_user_jsonb = ('高橋健', ['ゲーム', 'アニメ'])
    cur.execute("INSERT INTO users_jsonb (name, interests) VALUES (%s, %s::jsonb)", new_user_jsonb)
    conn.commit()
    print(f"{new_user_jsonb[0]} の情報を JSONB で追加しました。")

    # JSONBデータを取得して表示
    cur.execute("SELECT name, interests FROM users_jsonb WHERE name = %s", ('高橋健',))
    user_data_jsonb = cur.fetchone()
    print(f"{user_data_jsonb[0]} の興味: {user_data_jsonb[1]}")

except psycopg2.Error as e:
    print(f"データベースエラーが発生しました: {e}")

finally:
    if conn:
        cur.close()
        conn.close()

JSON/JSONBの利点

部分的なインデックス
JSONB型では、特定のキーやパスに対するインデックスを作成できます。
豊富な操作関数
PostgreSQLはJSON/JSONBデータを操作するための多くの関数を提供しています。
柔軟な構造
配列だけでなく、キーと値のペアを持つオブジェクトも格納できます。

JSON/JSONBの注意点

配列固有の操作関数（array_length, unnest など）は直接使用できません。
データ型がスキーマレベルで厳密に定義されないため、アプリケーション側でデータの整合性を管理する必要があります。

関連テーブルを使用する (正規化)

配列に格納するような複数の関連するデータがある場合、それらを別のテーブルに格納し、外部キーで元のテーブルと関連付けるという方法があります。これはデータベースの正規化の原則に基づいた設計です。

SQLの例

-- usersテーブル
CREATE TABLE users_normalized (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

-- interestsテーブル
CREATE TABLE interests_normalized (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users_normalized(id),
    interest VARCHAR(100)
);

-- データを挿入
INSERT INTO users_normalized (name) VALUES ('吉田一郎');
INSERT INTO interests_normalized (user_id, interest) VALUES
    ((SELECT id FROM users_normalized WHERE name = '吉田一郎'), '読書'),
    ((SELECT id FROM users_normalized WHERE name = '吉田一郎'), '料理');

-- 特定のユーザーの興味を取得
SELECT u.name, i.interest
FROM users_normalized u
JOIN interests_normalized i ON u.id = i.user_id
WHERE u.name = '吉田一郎';
-- 結果:
-- 吉田一郎 | 読書
-- 吉田一郎 | 料理

関連テーブルの利点

スケーラビリティ
大量の関連データを扱う場合に、一般的に配列よりも効率的です。
柔軟なクエリ
複雑な条件での検索や集計が容易になります。
データの整合性
外部キー制約により、データの整合性を保つことができます。

関連テーブルの注意点

複数のテーブルを結合する必要があるため、単純な配列アクセスよりもクエリが複雑になる場合があります。

キー-バリューペアのテーブルを使用する (EAVモデル)

属性名と値のペアを格納するテーブルを作成する方法です。配列内の要素が異なる意味を持つ場合に有効です。

SQLの例

CREATE TABLE user_attributes (
    user_id INTEGER REFERENCES users_normalized(id),
    attribute_name VARCHAR(100),
    attribute_value TEXT,
    PRIMARY KEY (user_id, attribute_name)
);

INSERT INTO users_normalized (name) VALUES ('小林さゆり');
INSERT INTO user_attributes (user_id, attribute_name, attribute_value) VALUES
    ((SELECT id FROM users_normalized WHERE name = '小林さゆり'), 'favorite_color', '青'),
    ((SELECT id FROM users_normalized WHERE name = '小林さゆり'), 'favorite_food', '寿司'),
    ((SELECT id FROM users_normalized WHERE name = '小林さゆり'), 'hobbies', '絵を描く, 旅行');

SELECT u.name, ua.attribute_name, ua.attribute_value
FROM users_normalized u
JOIN user_attributes ua ON u.id = ua.user_id
WHERE u.name = '小林さゆり';
-- 結果:
-- 小林さゆり | favorite_color | 青
-- 小林さゆり | favorite_food  | 寿司
-- 小林さゆり | hobbies        | 絵を描く, 旅行

キー-バリューペアテーブルの利点

柔軟性
事前にすべての属性を定義する必要がなく、動的に属性を追加できます。

キー-バリューペアテーブルの注意点

データの型が一つのカラムに混在するため、データの整合性を保つのが難しい場合があります。
クエリが複雑になりやすく、パフォーマンスが低下する可能性があります。

動的な属性
キー-バリューペアテーブルが選択肢となりますが、慎重な設計が必要です。
データの整合性や複雑な関連
関連テーブルを使用する正規化された設計が推奨されます。
柔軟な構造や複雑な検索
JSON/JSONB型が適しています。
単純な同じ型のリスト
PostgreSQLの配列が最も直接的で簡潔な方法です。

ネットワーク管理の効率化に役立つ！PostgreSQLのmacaddr8データ型の使い方とサンプルコード集

注意点:MACアドレス形式に準拠した値のみ格納できる誤った形式の値を格納するとエラーが発生するMACアドレス形式に準拠した値のみ格納できる誤った形式の値を格納するとエラーが発生する利点:MACアドレスを専用のデータ型で効率的に保存できるデータの整合性を保証しやすい

GIS入門：PostgreSQLで描く多角形 - ポリゴンデータ型の基礎から応用まで

このガイドでは、PostgreSQLにおけるポリゴンデータ型、その使用方法、および関連する関数について詳しく説明します。ポリゴンデータ型は、平面上の閉じた図形を表すために使用されます。複数の線で接続された3つ以上の座標点で定義されます。ポリゴンは、土地の境界線、建物、湖など、さまざまな形状を表現するために使用できます。

PostgreSQLでタイムゾーン付き時刻を扱う：Data Types: time with time zoneを徹底解説

time with time zone は、PostgreSQL における時間データ型の一つで、時刻とタイムゾーン情報を一緒に保持します。これは、様々なタイムゾーンにおける時刻を扱う必要がある場合に非常に有用です。例えば、国際的なビジネスを行っている場合、顧客やオフィスの所在地に応じて異なるタイムゾーンでの時刻を処理する必要があります。time with time zone 型を使用すると、このような状況で発生するデータの複雑さを軽減することができます。

PostgreSQLで日付と時刻を扱う：timestamp型とtimestamptz型の違い

小数点以下の秒数 (オプション)秒分時日月年注意点timestamp 型は、μ秒精度 (100万分の1秒) をサポートしていますが、2000年と2038年の間の範囲でのみ有効です。内部的には、timestamp 型の値は 2000-01-01 00:00:00 UTC からの経過秒数として格納されます。

PostgreSQLバイナリデータ型の落とし穴：よくあるエラーとトラブルシューティング

PostgreSQLでバイナリデータを扱うための主要なデータ型は以下の通りです。入出力フォーマット: bytea 型は、入出力時に以下の2つのフォーマットをサポートしています。 Hex (16進) フォーマット: 各バイトを2桁の16進数で表現し、文字列全体は \x というプレフィックスで始まります。例: \x012345ab 可読性が高く、多くの外部アプリケーションやプロトコルとの互換性が高いため、推奨されるフォーマットです。 PostgreSQL 9.0以降で導入されました。 Escape (エスケープ) フォーマット: 従来のPostgreSQLのフォーマットで、バイナリデータをASCII文字の並びとして表現し、ASCII文字として表現できないバイトは特殊なエスケープシーケンス（例: \000 のように3桁の8進数とバックスラッシュ）で表現します。例: E'\\001\\002\\003' バイナリと文字列の区別があいまいになることや、エスケープの仕組みが扱いにくいことから、新しいアプリケーションではあまり推奨されません。

PostgreSQLのbytea型徹底解説：バイナリデータの基本と活用法

byteaは「バイナリ列（Binary Array）」の略で、任意のバイナリ文字列（バイト列）をそのまま格納できます。テキストデータ型（textやvarcharなど）が文字セットのエンコーディング規則に従うのに対し、byteaはバイト列そのものを扱うため、エンコーディングによる変換を受けません。

PostgreSQLの「Character Types: character」データ型の代替方法

character(n) データ型は、n 文字までの文字列を格納できます。n は、正の整数である必要があります。文字列の長さが n 文字よりも短い場合、空白で埋められます。例この例では、users テーブルに name という名前の列が作成されます。この列は character(50) データ型であり、最大50文字までの文字列を格納できます。ユーザー名が50文字よりも短い場合、空白で埋められます。

PostgreSQLの「Character Types: text」を徹底解説！長所・短所、代替案まで網羅

'text'型は、長さ制限なしの可変長文字列を格納するために使用されます。つまり、数行から数GBに及ぶような長文であっても問題なく保存できます。これは、ブログ記事、文書、ログデータなど、長さを予期できないテキストデータを扱うのに最適です。'text'型の内部表現は、他の可変長文字列型とは異なり、1バイト単位で区切られた文字列のシーケンスとして格納されます。このため、非常に長いテキストであっても効率的に圧縮することができ、ストレージスペースを節約することができます。

【保存形式が特殊】PostgreSQLのtimestamp with time zone型を使いこなすための5つのポイント

PostgreSQLのtimestamp with time zoneデータ型は、日時情報をタイムゾーン付きで保持するために使用されます。これは、時刻だけでなく、その時刻がどのタイムゾーンで表現されているのかを記録するものです。内部表現興味深いことに、timestamp with time zone型で格納される値は、**常に協定世界時 (UTC)**で表現されます。つまり、たとえ日本時間 2024-07-09 14:23:00 と入力されたとしても、内部的には 2024-07-09 06:23:00+00 (UTC) として保存されます。

GIS初心者でも安心！PostgreSQLで線分データを扱うためのチュートリアル

line 型は、2つの点を通る無限長の直線を表します。これらの点は、それぞれ (x1, y1) と (x2, y2) のような座標ペアで定義されます。(x2, y2): 線分の終点座標(x1, y1): 線分の始点座標線分は方向を持ち、始点から終点に向かって伸びています。また、線分の長さは、2つの点間の距離として計算できます。