AIと学ぶPHPの学校
ch12 12章中 12章目

ch12 — PostgreSQL 固有の文法と機能

スライドで見る

この章を AI と学ぶ

💬 AI に聞く質問例 3 件

  1. 「ch12 — PostgreSQL 固有の文法と機能」のつまずきやすいポイントを、初学者向けに3つ教えてください

    ChatGPT Claude Gemini

  2. 「ch12 — PostgreSQL 固有の文法と機能」について、自分の手で試せる練習問題を3つ出してください (難易度を上げながら)

    ChatGPT Claude Gemini

  3. 「ch12 — PostgreSQL 固有の文法と機能」を学んだ後、次に学ぶと良いトピックは何ですか？理由も教えてください

    ChatGPT Claude Gemini

概要

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 固有) を学ぶと比較で理解が深まる

ドリル

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 を参照してください。

解説スライド 全 11 枚

右の › ボタンか → キー(またはスワイプ)で次のスライドに進めます。 下の「縦表示」を開くと全スライドを一気に読めます。

  1. PostgreSQL 固有の文法と機能

    Lesson 11 / Chapter 12

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

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

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

  3. 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 が広く使われる。

  4. 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() で受け取る。

  5. \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 からは叩けない。

  6. データ型のクイックリファレンス

    用途 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 のような暗黙変換は基本的にエラー。

  7. 配列型 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 インデックス が貼れる。

  8. 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 インデックス可
    用途 原文ログ保管 通常はこちら

  9. ハマりどころ 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 は破綻する。

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

  10. ハマりどころ 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 区別しない) と更にややこしい。

  11. このチャプターでできるようになること

    ✅ SERIAL / BIGSERIAL で連番 ID を発行できる ✅ RETURNING 句で INSERT 直後の値を 1 ステートメントで取れる ✅ \dt / pg_tables でテーブル一覧を取れる ✅ 主要データ型 (INTEGER / TEXT / TIMESTAMPTZ / BOOLEAN) を選べる ✅ 配列型 TEXT[] と ANY() 演算子でタグ検索できる ✅ JSONB の ->> / -> で JSON のキーを抽出できる ✅ JSONB vs JSON を使い分けられる

    → ドリル 3 問へ (SQLite で動かしつつ PostgreSQL 固有の構文を理解)

1 / 11
📜 スライドを全部一気に読む(縦表示)

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 は破綻する。

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

ハマりどころ 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 固有の構文を理解)

この章で取り組む 演習問題の流れ (3ステップ)
  1. 01 ドリル 01 — `SERIAL` + `RETURNING` で 1 ステートメント採番 README.md starter.php answer.php
  2. 02 ドリル 02 — タグ配列を扱う (PostgreSQL `TEXT[]`) README.md starter.php answer.php
  3. 03 ドリル 03 — JSON 内の値で検索する (PostgreSQL `JSONB`) README.md starter.php answer.php

演習問題の詳細

この章の演習問題の内容を読めます。実際に手元で解くには教材リポジトリを clone してください。

ドリル 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

ヒント

  • 採用ドライバは 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 で取り直す」が不要になる。

🧪 ブラウザで試す

スライドのコードを写経して ▶ 実行、自分で書いて 📝 採点 で答え合わせ。初回のみ PHP ランタイム読込で数秒かかります。

PHP ランタイム未読込 

          

        

              

          

        

          ドリル 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入門


ヒント


    

  • 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 など) を持たせたい」なら中間テーブルが必要なので 使い分け が肝心。
        

                        

          
🧪 ブラウザで試す

          
スライドのコードを写経して ▶ 実行、自分で書いて 📝 採点 で答え合わせ。初回のみ PHP ランタイム読込で数秒かかります。

          
          

            
            
            
            PHP ランタイム未読込
          

          

          

        

              

          

        

          ドリル 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


ヒント


    

  • 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 が便利。ただし「検索する属性が決まったら」普通のカラムに切り出した方が高速。
        

                        

          
🧪 ブラウザで試す

          
スライドのコードを写経して ▶ 実行、自分で書いて 📝 採点 で答え合わせ。初回のみ PHP ランタイム読込で数秒かかります。

          
          

            
            
            
            PHP ランタイム未読込
          

          

          

        

              

      

  
    

    
 演習問題(3問)

    
    
              
  1. 
          
    ドリル 01 — `SERIAL` + `RETURNING` で 1 ステートメント採番
    
          
    
                          README.md
                          starter.php
                          answer.php
                      
    
        
    2. 
              
  2. 
          
    ドリル 02 — タグ配列を扱う (PostgreSQL `TEXT[]`)
    
          
    
                          README.md
                          starter.php
                          answer.php
                      
    
        
    3. 
              
  3. 
          
    ドリル 03 — JSON 内の値で検索する (PostgreSQL `JSONB`)
    
          
    
                          README.md
                          starter.php
                          answer.php
                      
    
        
    4. 
          

    
サイト内で問題文・雛形・解答例を確認できます。実際に手元で解くには教材リポジトリを clone してください。

  

  
  
  
  
  

  

  

    
    
完了にすると進捗に反映されます