PostgreSQL 固有の文法と機能
テクノロジ系 ／ db (PDO / SQL / マルチDB)
PostgreSQL 固有の文法と機能
Lesson 11 / Chapter 12
なぜ PostgreSQL を使うのか (MySQL との選択ポイント)
| 場面 | PostgreSQL の強み |
|---|---|
| 採番後すぐ id を使いたい | RETURNING 句 (1 ステートメント) |
| タグなど可変長の値 | 配列型 (TEXT[]) で 1 カラム表現 |
| 半構造化データ | JSONB で検索・GIN インデックス可 |
| 厳密な型システム | 文字列 → 数値の暗黙変換が無い・型エラーで気付ける |
| 高度な SQL | CTE / Window 関数 / GENERATED ALWAYS |
→ 「データ整合性 + 半構造化への対応」が要件なら PostgreSQL。
SERIAL で連番 ID を発行する
-- PostgreSQL
CREATE TABLE notes (
id SERIAL PRIMARY KEY, -- 内部でシーケンスを作って 1, 2, 3, ... を採番
body TEXT NOT NULL
);| 内部の正体 | SEQUENCE + DEFAULT nextval('notes_id_seq') |
|---|---|
| 大きい型が欲しい | BIGSERIAL (= BIGINT 連番) |
| MySQL 相当 | INT AUTO_INCREMENT |
PostgreSQL 10 以降は GENERATED ALWAYS AS IDENTITY が推奨 (標準SQL準拠) だが、現場では SERIAL が広く使われる。
RETURNING 句 — INSERT 直後の値を 1 往復で取る
INSERT INTO notes (body)
VALUES ('hello')
RETURNING id, created_at;| 観点 | MySQL lastInsertId() | PostgreSQL RETURNING |
|---|---|---|
| 取れる値 | AUTO_INCREMENT 1 つだけ | 任意のカラム複数 |
| 往復回数 | INSERT + 取得 | 1 ステートメント |
UPDATE / DELETE | 不可 | 可 (更新前後の値を取れる) |
PHP からは $stmt->execute(...) 後に $stmt->fetchColumn() / $stmt->fetch() で受け取る。
\dt と pg_tables — テーブル一覧
| 用途 | PostgreSQL | MySQL |
|---|---|---|
| psql 上で一覧 | \dt | SHOW TABLES; |
| SQL でプログラマブルに | SELECT tablename FROM pg_tables WHERE schemaname='public'; | SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = DATABASE(); |
| カラム定義を見たい | \d notes | DESCRIBE notes; |
\d, \dt, \dn などの バックスラッシュコマンド は psql クライアントの機能。SQL ではないので PHP からは叩けない。
データ型のクイックリファレンス
| 用途 | PostgreSQL | MySQL 相当 |
|---|---|---|
| 小さい整数 | SMALLINT | SMALLINT |
| 普通の整数 | INTEGER | INT |
| 大きい整数 | BIGINT | BIGINT |
| 長文 | TEXT (長さ無制限) | TEXT (型でサイズ別) |
| 短文 | VARCHAR(n) | VARCHAR(n) |
| 日時 (TZ 無視) | TIMESTAMP | DATETIME |
| 日時 (TZ あり) | TIMESTAMPTZ | (なし・アプリで管理) |
| 真偽値 | BOOLEAN | TINYINT(1) で代用が多い |
PostgreSQL は 型に厳密。'5' + 3 のような暗黙変換は基本的にエラー。
配列型 TEXT[] — タグを 1 カラムで持つ
CREATE TABLE articles (
id INT PRIMARY KEY,
title TEXT NOT NULL,
tags TEXT[] NOT NULL
);
INSERT INTO articles VALUES (1, 'PHP入門', ARRAY['php','tutorial']);
-- 'php' を含む記事を取る
SELECT title FROM articles WHERE 'php' = ANY(tags);| 演算子 | 意味 | ||
|---|---|---|---|
'x' = ANY(arr) | x が含まれる | ||
arr1 @> arr2 | arr1 は arr2 を含む (集合の包含) | ||
arr1 && arr2 | 共通要素あり (積集合 ≠ ∅) | ||
| `arr1 \ | \ | arr2` | 連結 |
中間テーブル不要で GIN インデックス が貼れる。
JSONB — 検索可能な JSON
CREATE TABLE products (
id INT PRIMARY KEY,
spec JSONB NOT NULL
);
-- brand を取り出す
SELECT spec->>'brand' FROM products; -- text として
SELECT spec->'brand' FROM products; -- jsonb のまま
-- brand = 'acme' で検索
SELECT * FROM products WHERE spec->>'brand' = 'acme';| 観点 | JSON | JSONB |
|---|---|---|
| 表現 | テキスト | バイナリ (正規化) |
| 空白・キー順 | 保持 | 失う |
| 重複キー | 保持 | 後勝ち |
| インデックス | 不可 | GIN インデックス可 |
| 用途 | 原文ログ保管 | 通常はこちら |
ハマりどころ 1 — TIMESTAMPTZ vs TIMESTAMP
TIMESTAMP -- タイムゾーン情報を持たない (壁時計のような値) TIMESTAMPTZ -- UTC で保存・取得時にセッション TZ に変換
TIMESTAMP '2026-05-18 12:00'→ どこの12時か分からないTIMESTAMPTZ '2026-05-18 12:00+09'→ UTC 03:00 として保存
実務では基本 TIMESTAMPTZ を選ぶ。複数タイムゾーンのユーザーがいると TIMESTAMP は破綻する。
ハマりどころ 2 — 識別子の大文字小文字
CREATE TABLE Users (id INT); -- 実体は "users" に小文字化される SELECT * FROM "Users"; -- ERROR (実体は users なので) SELECT * FROM Users; -- OK (小文字化されてマッチ) SELECT * FROM "users"; -- OK
- PostgreSQL は クォートしない識別子をデフォルトで小文字に畳む
- ダブルクォートで囲うと大文字小文字を保持するが「以降ずっとクォートが必要」になる
- → 常に snake_case (
users,created_at) で書く のが鉄則
MySQL は OS の大文字小文字区別設定に依存 (Linux 大小区別 / macOS 区別しない) と更にややこしい。
このチャプターでできるようになること
✅ SERIAL / BIGSERIAL で連番 ID を発行できる
✅ RETURNING 句で INSERT 直後の値を 1 ステートメントで取れる
✅ \dt / pg_tables でテーブル一覧を取れる
✅ 主要データ型 (INTEGER / TEXT / TIMESTAMPTZ / BOOLEAN) を選べる
✅ 配列型 TEXT[] と ANY() 演算子でタグ検索できる
✅ JSONB の ->> / -> で JSON のキーを抽出できる
✅ JSONB vs JSON を使い分けられる
→ ドリル 3 問へ (SQLite で動かしつつ PostgreSQL 固有の構文を理解)
考えてみよう
既定では正解・不正解の判定はしません。自分のペースで「答えを見る」を。採点してほしい場合だけ「採点してほしい」を押してください。
ドリル 01 — SERIAL + RETURNING で 1 ステートメント採番
問題
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
採点
php scripts/grade.php topics/db/ch12-postgres-specific/drill/01-serial-returning/
採点ランナーはデフォルトで SQLite を使うので、AUTOINCREMENT カラムへの INSERT → lastInsertId() の経路で PASS する。
PostgreSQL モード (DOJO_DB_DRIVER=pgsql) で動かしたときは tests/setup.pgsql.sql が選ばれ、SERIAL + RETURNING の経路を通る。
ヒント
- 採用ドライバは
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 で取り直す」が不要になる。
ドリル 02 — タグ配列を扱う (PostgreSQL TEXT[])
問題
articles テーブルから タグに "php" を含む記事の title を id 順で出力する。
PHP入門 Laravel入門
タグの格納形式は driver で異なる:
| driver | カラム型 | 中身 |
|---|---|---|
| PostgreSQL | TEXT[] (配列型) | ARRAY['php','tutorial','beginner'] |
| MySQL | JSON (配列型は無いので JSON で代替) | '["php","tutorial","beginner"]' |
| SQLite | TEXT (JSON 文字列) | '["php","tutorial","beginner"]' |
→ WHERE 句も driver で変わる。
| driver | WHERE 句 |
|---|---|
| PostgreSQL | 'php' = ANY(tags) |
| MySQL | JSON_CONTAINS(tags, '"php"') |
| SQLite | EXISTS (SELECT 1 FROM json_each(tags) WHERE value = 'php') |
期待される出力
PHP入門 Laravel入門
採点
php scripts/grade.php topics/db/ch12-postgres-specific/drill/02-text-array/
デフォルトで SQLite 経路 (json_each) が走って PASS する。
ヒント
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 など) を持たせたい」なら中間テーブルが必要なので 使い分け が肝心。
ドリル 03 — JSON 内の値で検索する (PostgreSQL JSONB)
問題
products テーブルから spec.brand が 'acme' の商品を id 順で抽出し、name: brand の形式で出力する。
ノートPC: acme マウス: acme
driver で JSON 抽出のシンタックスが異なる:
| driver | カラム型 | brand 抽出 |
|---|---|---|
| PostgreSQL | JSONB | spec->>'brand' |
| MySQL | JSON | JSON_UNQUOTE(JSON_EXTRACT(spec, '$.brand')) |
| SQLite | TEXT (JSON 文字列) | json_extract(spec, '$.brand') |
期待される出力
ノートPC: acme マウス: acme
採点
php scripts/grade.php topics/db/ch12-postgres-specific/drill/03-jsonb-or-json/
ヒント
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)
| 観点 | JSON | JSONB |
|---|---|---|
| 内部表現 | テキスト (入力そのまま) | バイナリ (正規化) |
| 入力時の空白 | 保持 | 削除 |
| キーの重複 | 保持 | 最後の値で上書き |
| 順序 | 保持 | 失われる |
| インデックス | 不可 | GIN インデックス可 |
| 検索性能 | 遅い (パース必須) | 速い |
| 推奨 | ログのような「原文保存」用途 | 検索・更新する JSON は JSONB |
→ 実務では基本的に JSONB を選ぶ。
なぜ JSON 型を使うか
「商品ごとに任意のスペックを持つ」ような スキーマレス な要件で、
- 中間テーブルを作るほどでもない
- カラムを増やすほど明確でもない
ようなときに JSONB が便利。ただし「検索する属性が決まったら」普通のカラムに切り出した方が高速。