概要

PostgreSQL は 「データの厳密性と表現力」 で選ばれる RDB で、業務系・金融系・分析基盤・Supabase などのモダン PaaS でも採用が広い。MySQL より仕様遵守が厳しく、型・スキーマ・トランザクションの挙動が予測しやすいので、データ整合性を守りたい案件で第一候補になる。

固有でまず覚えるのが、自動採番の SERIAL / BIGSERIAL と、INSERT/UPDATE/DELETE に付けられる RETURNING 句。INSERT ... RETURNING id で追加行の値を 1 ステートメント で取り出せる (MySQL なら lastInsertId() を別途呼ぶ流れが 1 つにまとまる)。

データ型の表現力も特徴で、TEXT / TIMESTAMPTZ (TZ 付き時刻) / BOOLEAN / TEXT[] (配列型) / JSONB (バイナリ正規化済み JSON) などが標準で揃う。JSONB は -> ->> @> 演算子と GIN インデックスで半構造データを速く扱う武器で、Supabase / Hasura でも要。識別子の大文字小文字も厳密 ("Name" と name は別物) など、MySQL と細部の流儀が違う。

学習目標

• SERIAL / BIGSERIAL で連番 ID を発行する (MySQL の AUTO_INCREMENT 相当)
• \dt と pg_tables でテーブル一覧を取得できる
• RETURNING 句 で INSERT / UPDATE / DELETE の結果値を 1 ステートメントで取得できる
• データ型: SMALLINT / INTEGER / BIGINT / TEXT / VARCHAR(n) / TIMESTAMP / TIMESTAMPTZ / BOOLEAN を使い分けられる
• 配列型 (TEXT[]) を扱える
• JSONB と JSON の違い (JSONB はインデックス可・バイナリ正規化済み) を説明できる

前提

• db トピック ch01〜ch10 完了
• 特に ch10 (DSN 比較 / 複数 DB 接続) の理解が前提
• 並列章として ch11 (MySQL 固有) を学ぶと比較で理解が深まる

ドリル

採点用 DB について

ch10 と同じく、採点ランナーは SQLite を default に動かします。tests/setup.sql (SQLite 用) を一時 SQLite に流し込み、そのファイルパスを DOJO_DB_PATH に渡してきます。

PostgreSQL / MySQL での動作確認は tests/setup.pgsql.sql / tests/setup.mysql.sql を採点ランナーが優先選択します (DOJO_DB_DRIVER=pgsql 等を指定したとき)。

このため answer.php は driver 別に分岐 することで「同じ問題を SQLite / MySQL / PostgreSQL で動かす」ことを実演します。

Docker で PostgreSQL を試したい場合

実際の PostgreSQL コンテナに接続して動作確認したい場合は、リポジトリ直下の docker/README.md を参照してください。

問題

notes テーブルに body を 3 件 INSERT し、INSERT 直後の id を取得して 1 件ずつ次のフォーマットで出力する。

inserted id: <id>

PostgreSQL では INSERT ... RETURNING id が使えるため INSERT と id 取得が 1 ステートメント で済む。MySQL / SQLite には RETURNING が無い (※) ので、INSERT → lastInsertId() の 2 段階になる。

※ MySQL 8 / SQLite 3.35 で RETURNING がサポートされたが、ポータビリティのため本ドリルでは lastInsertId() で書く想定。

期待される出力

inserted id: 1
inserted id: 2
inserted id: 3

ヒント

• 採用ドライバは getenv('DOJO_DB_DRIVER') ?: 'sqlite' で判定する
• PostgreSQL: RETURNING id の戻り値は $stmt->fetchColumn() で 1 つの値として取れる
• SQLite / MySQL: INSERT 後に $pdo->lastInsertId() で取得する
• ループで body を 3 回 INSERT すれば id は 1, 2, 3 と採番される
• scripts/shared/db-connect.php の dojo_db_connect() を使うと PDO を driver 非依存に取得できる

なぜ PostgreSQL の RETURNING が便利か

INSERT した行の (シーケンスで採番された) id や created_at などの DB 側で自動入力された値を 1 往復 で取得できる。MySQL の lastInsertId() は AUTO_INCREMENT カラム 1 つに限定だが、RETURNING は 任意のカラム を返せる:

INSERT INTO notes (body) VALUES ('hello')
RETURNING id, created_at, edit_token;

→ 「INSERT 後に SELECT で取り直す」が不要になる。

問題

articles テーブルから タグに "php" を含む記事の title を id 順で出力する。

PHP入門
Laravel入門

タグの格納形式は driver で異なる:

→ WHERE 句も driver で変わる。

期待される出力

PHP入門
Laravel入門

ヒント

• getenv('DOJO_DB_DRIVER') ?: 'sqlite' で分岐
• SQLite の json_each(tags) は JSON 配列を行に展開するテーブル値関数
• PostgreSQL の ANY(配列) は 配列内のどれかと一致 という意味
• 結果は ORDER BY id で並べる
• fetchAll(PDO::FETCH_COLUMN) で title 列だけ取り出すと echo が楽

なぜ配列型を持つ DB は便利か

タグや「複数選択値」を「中間テーブル + JOIN」せずに 1 カラム で表せる。

• 中間テーブル不要 → クエリがシンプル
• GIN インデックスを張れる → WHERE 'php' = ANY(tags) が高速
• 配列演算子 (@>, &&, ||) で集合演算ができる

ただし「タグごとに属性 (created_by など) を持たせたい」なら中間テーブルが必要なので 使い分け が肝心。

問題

products テーブルから spec.brand が 'acme' の商品を id 順で抽出し、name: brand の形式で出力する。

ノートPC: acme
マウス: acme

driver で JSON 抽出のシンタックスが異なる:

期待される出力

ノートPC: acme
マウス: acme

ヒント

• getenv('DOJO_DB_DRIVER') ?: 'sqlite' で分岐
• PostgreSQL の -> は JSON のまま 返し、->> は テキスト で返す。比較したいときは ->>
• SQLite の json_extract(col, '$.key') は組み込み関数 (3.38 以降は col -> '$.key' でも OK)
• MySQL の JSON_EXTRACT() は二重引用符付き値を返すので JSON_UNQUOTE() で剥がす
• 出力フォーマット: name: brand (コロンの後ろにスペース 1 つ)
• ORDER BY id

JSONB と JSON の違い (PostgreSQL)

→ 実務では基本的に JSONB を選ぶ。

なぜ JSON 型を使うか

「商品ごとに任意のスペックを持つ」ような スキーマレス な要件で、

• 中間テーブルを作るほどでもない
• カラムを増やすほど明確でもない

ようなときに JSONB が便利。ただし「検索する属性が決まったら」普通のカラムに切り出した方が高速。