# ch12 — PostgreSQL 固有の文法と機能

Source: https://php-school.pages.dev/topics/db/ch12-postgres-specific/
Site: AIと学ぶPHPの学校 (https://php-school.pages.dev)
Lesson: データベース連携
Level: L4

Reference (official): https://www.php.net/manual/ja/book.pdo.php

## 本文

# ch12 — PostgreSQL 固有の文法と機能

## 概要

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` はインデックス可・バイナリ正規化済み) を説明できる

## 所要時間

スライド 6 分 + ドリル 3 問 = **約 1.5 時間**

## 前提

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

## ドリル

| no | 内容 |
|---|---|
| 01 | `SERIAL` + `RETURNING id` で 1 ステートメント採番 |
| 02 | `TEXT[]` 配列型 (SQLite では JSON テキストで代替) |
| 03 | `JSONB` のキー抽出 (SQLite では `json_extract` で代替) |

## 採点用 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` を参照してください。

## スライド

---
marp: true
theme: dojo
paginate: true
size: 16:9
---

<!--
LLM_CONTEXT:
  Lesson 11 / Chapter 12
  目的: PostgreSQL 固有の文法・機能 (SERIAL / RETURNING / 配列型 / JSONB / 厳密な型) を把握する
  前提: ch01-ch10 完了済み (PDO / prepare / fetch / トランザクション / DSN 切り替え)
  並列章: ch11 (MySQL 固有) — 比較して読むと理解が深まる
  扱わない: パフォーマンスチューニング / VACUUM / レプリケーション
  読み上げ時間目安: 6 分
-->

# PostgreSQL 固有の文法と機能

Lesson 11 / Chapter 12

---

## なぜ PostgreSQL を使うのか (MySQL との選択ポイント)

| 場面 | PostgreSQL の強み |
|---|---|
| 採番後すぐ id を使いたい | **`RETURNING` 句** (1 ステートメント) |
| タグなど可変長の値 | **配列型 (`TEXT[]`)** で 1 カラム表現 |
| 半構造化データ | **`JSONB`** で検索・GIN インデックス可 |
| 厳密な型システム | 文字列 → 数値の暗黙変換が無い・型エラーで気付ける |
| 高度な SQL | CTE / Window 関数 / `GENERATED ALWAYS` |

→ 「データ整合性 + 半構造化への対応」が要件なら PostgreSQL。

---

## `SERIAL` で連番 ID を発行する

```sql
-- 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 往復で取る

```sql
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 カラムで持つ

```sql
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

```sql
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`

```sql
TIMESTAMP        -- タイムゾーン情報を持たない (壁時計のような値)
TIMESTAMPTZ      -- UTC で保存・取得時にセッション TZ に変換
```

- `TIMESTAMP '2026-05-18 12:00'` → どこの12時か分からない
- `TIMESTAMPTZ '2026-05-18 12:00+09'` → UTC 03:00 として保存

**実務では基本 `TIMESTAMPTZ`** を選ぶ。複数タイムゾーンのユーザーがいると `TIMESTAMP` は破綻する。

| MySQL 比較 | MySQL の `DATETIME` は `TIMESTAMP` (TZ 無視) 寄り。`TIMESTAMP` カラムだけ UTC 変換するが TZ 情報は保存しない |

---

## ハマりどころ 2 — 識別子の大文字小文字

```sql
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 固有の構文を理解)