--- note: Dockerで PostgreSQL を立て教材データを投入。エンコーディング・照合順序という一方通行の初期決定 created: 2026-08-14T10:00:00+09:00 --- # 第1回｜環境構築・エンコーディング/照合順序の初期決定事項 > データベースを作った瞬間に、後から変えにくい決定がいくつか確定する。この回ではそれを体で覚える。 ## この回のねらい この回は、Docker で PostgreSQL 16 を起動し、psql から接続し、全13回で使い続ける教材データを投入するところまでを一気にやる。単なる環境構築ではない。`CREATE DATABASE` の瞬間に確定する2つの決定——エンコーディングと照合順序（collation）——が、あとから軽い気持ちでは変更できない一方通行の決定であることを、実際にエラーを起こしながら理解する。この回で作る環境とデータは、第2回以降すべての回の土台になる。 ## 到達目標 - Docker で PostgreSQL 16 のコンテナを起動し、psql で接続できる - エンコーディングと `LC_COLLATE`/`LC_CTYPE` を明示して自分でデータベースを作成できる - 共通スキーマ（7テーブル）を作成し、`generate_series` で教材データを投入できる - `\l` でデータベースのエンコーディングと `LC_COLLATE`/`LC_CTYPE` を確認できる - なぜ PostgreSQL で UTF-8 を選ぶのかを一言で説明できる - `C` 照合順序と ICU 照合順序（`ja-x-icu` など）の違いを一言で説明でき、実際に `ORDER BY` の結果が変わることを確認できる - `timestamptz` と `timestamp` の違いと、`timestamptz` を選ぶべき理由を説明できる ## 前提と準備 この回だけは「これから環境を作る」立場で書く。以下がホスト側に必要な前提である。 - Docker Desktop（または Docker Engine）がインストール済みで、ターミナルから `docker` コマンドが使えること - ホストに `psql` クライアントを別途インストールする必要はない。`docker exec` 経由でコンテナ内の `psql` を使う - 初回は `postgres:16` イメージの pull が発生する（数百MB）。教材データを全部投入すると、データベースのサイズはおおよそ1GB程度になる（`generate_series` で1,000万行規模のテーブルを作るため） - ローカルの `5432` 番ポートが他のプロセス（ローカルにインストール済みの PostgreSQL など）に使われていないことを確認しておく。使われている場合の対処は「よくあるつまずきと対処」で扱う ## この回で新しく出てくる用語 本編のスキーマ定義とデータ投入で、説明なしに出てくる記法・構文をここで押さえる。回をまたいで使う語は[用語集](glossary.md)にもまとめている。 | 用語 | 一行での説明 | |---|---| | psql メタコマンド | psql が解釈するバックスラッシュ始まりのコマンド。SQL ではない | | 照合順序（collation） | 文字列の大小比較・並び替えの規則 | | GUC | サーバやセッションの動作を変える実行時パラメータの総称（Grand Unified Configuration） | | `GENERATED BY DEFAULT AS IDENTITY` | 値を省略したとき連番を自動採番する列の宣言。明示的な値の挿入も許す | | `::`（キャスト） | 値を別の型として解釈し直す記法。`式::型名` と書く | | `\|\|` | 文字列どうしを連結する演算子 | | `ARRAY[...]` | 配列を作るリテラル記法。`(ARRAY[...])[n]` で n 番目を取り出す | | `interval` | 「3日」「1年」のような期間・長さを表す型。`interval '3 days'` と書く | | `generate_series(1, N)` | 1 から N までの整数を1行ずつ返す集合返却関数 | | `ON CONFLICT ... DO NOTHING` | 一意制約に衝突した行をエラーにせず黙って捨てる `INSERT` の指定 | ## 本編 ### Docker で PostgreSQL 16 を起動する まずコンテナを起動する。教材用データベースは自分で明示的に作りたいので、`POSTGRES_DB` は指定せず、`POSTGRES_PASSWORD` だけを与える。 ```bash docker run -d \ --name postshop-db \ -e POSTGRES_PASSWORD=postshop \ -p 5432:5432 \ -v postshop-data:/var/lib/postgresql/data \ postgres:16 ``` - `-v postshop-data:/var/lib/postgresql/data` は名前付きボリュームでデータを永続化する。無くても動くが、コンテナを消すたびにデータが消えるので付けておく - 起動直後は初期化中のことがあるので、次のコマンドで接続できる状態になるまで待つ ```bash docker exec postshop-db pg_isready -U postgres ``` `accepting connections` と出れば準備完了である。docker compose を使う場合は以下で同じ構成になる。 ```yaml # compose.yaml services: db: image: postgres:16 environment: POSTGRES_PASSWORD: postshop ports: - "5432:5432" volumes: - postshop-data:/var/lib/postgresql/data volumes: postshop-data: ``` ```bash docker compose up -d ``` ### psql に接続し、メタコマンドで様子を見る コンテナ内の `psql` にそのまま入る。 ```bash docker exec -it postshop-db psql -U postgres ``` psql にはバックスラッシュで始まる「メタコマンド」があり、SQL を書かずにカタログ情報を確認できる。序盤でよく使うものを覚えておく。 | メタコマンド | 意味 | |---|---| | `\l` | データベース一覧（エンコーディング・照合順序を含む） | | `\dt` | 現在のデータベースのテーブル一覧 | | `\d テーブル名` | テーブルの列定義・制約・インデックスを表示 | | `\c データベース名` | 接続先データベースを切り替える | | `\timing on` | 以降のクエリの実行時間を表示する（切り替えは `\timing` のみでも可） | | `\q` | psql を終了する | `\l` を打つと、この時点では `postgres`・`template0`・`template1` の3つしか無いはずである。 ```text List of databases Name | Owner | Encoding | Locale Provider | Collate | Ctype | ... -----------+----------+----------+-----------------+------------+------------+----- postgres | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | ... template0 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | ... template1 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | ... (3 rows) ``` `Locale Provider` が `libc`、`Collate`/`Ctype` が `en_US.utf8` になっている。これは `postgres:16` イメージが初期化（`initdb`）された時点のデフォルトである。ここに教材用の `postshop` データベースを、デフォルトに頼らず明示的な設定で作る。 ### エンコーディングと照合順序を明示してデータベースを作る `CREATE DATABASE` には、文字コード（`ENCODING`）と、文字列の比較・並び替えルールである照合順序（`LC_COLLATE`/`LC_CTYPE`、または ICU の `ICU_LOCALE`）を指定できる。教材データベースは次のように作る。 ```sql CREATE DATABASE postshop ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0; ``` 3つの選択について説明する。 **なぜ `ENCODING 'UTF8'` か。** エンコーディングは文字をバイト列にどう変換するかの規則である。教材データには日本語の地域名・商品名・カテゴリ名が含まれるため、日本語を含む Unicode 全体を表現できる符号化が必須であり、PostgreSQL の事実上の既定選択は UTF-8 である。`SQL_ASCII` のような「符号化を検証しない」設定を選ぶと、不正なバイト列がそのまま格納されてしまい、後述の「よくあるつまずき」の一つになる。 **なぜ `LC_COLLATE`/`LC_CTYPE` を `C` にするか。** `LC_COLLATE` は文字列の大小比較・ソート順を決め、`LC_CTYPE` は「これは英字か」「大文字小文字変換はどうするか」といった文字の分類を決める。`C`（`POSIX` も同義）は、文字をバイト値でそのまま比較する最も単純なルールで、次の性質を持つ。 - 言語に依存しない。どの環境で実行しても結果が変わらない - OS のロケールデータに依存しない。glibc（Linux の C ライブラリ）のロケールデータは OS のアップデートで書き換わることがあり、既存のインデックスが前提にしていた並び順とズレて、インデックスが実質的に壊れる（該当行を正しく見つけられなくなる）ことが知られている。`C` はこの問題を原理的に避けられる - 日本語の「読み仮名順」のような自然な並びにはならない。バイト値順＝おおよそ Unicode のコードポイント順になるだけである 一方、日本語を自然な感覚で並べたい場面（画面表示のソートなど）には ICU（International Components for Unicode）照合順序を使う。PostgreSQL は ICU ライブラリを内蔵しており（`--with-icu` でビルドされている場合。公式 Docker イメージは内蔵済み）、`ja-x-icu` のような言語別の照合順序が `initdb` 直後から `pg_collation` カタログに標準で用意されている。次のコマンドで確認できる。 ```sql SELECT collname FROM pg_collation WHERE collname LIKE 'ja%'; ``` ```text collname ------------- ja-JP-x-icu ja-x-icu (2 rows) ``` ICU 照合順序は OS のロケールとは独立して PostgreSQL 自身が持っているため、`ja_JP.UTF-8` のような OS ロケール名を `LC_COLLATE` に指定しても、そのロケールが OS 側に生成されていなければ失敗する。 ```sql CREATE DATABASE test_ja ENCODING 'UTF8' LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8' TEMPLATE template0; ``` ```text ERROR: invalid LC_COLLATE locale name: "ja_JP.UTF-8" HINT: If the locale name is specific to ICU, use ICU_LOCALE. ``` 公式 Docker イメージには `en_US.utf8` と `C` 系統のロケールしか生成されておらず、`ja_JP.UTF-8` は無いためである。エラーメッセージが示す通り、日本語の照合が必要なら `LC_COLLATE` に OS ロケール名を渡すのではなく、ICU を使う。データベース全体を ICU 照合にする場合は次のように書ける。 ```sql CREATE DATABASE postshop_icu ENCODING 'UTF8' LOCALE_PROVIDER icu ICU_LOCALE 'ja' LC_CTYPE 'C' TEMPLATE template0; ``` この講座では、データベース全体のデフォルトは安全な `C` にしておき、日本語の並び順が必要なクエリだけ `ORDER BY 列 COLLATE "ja-x-icu"` のように明示的に指定する方針を取る。デフォルトを ICU にすると、日本語の並び順が「便利だが遅く、かつ環境・バージョン依存になりうる」比較に全面的に切り替わってしまうためである（詳しくは[第7回](07-storage-indexes-collation.md)で扱う）。 **なぜ `TEMPLATE template0` が要るか。** `CREATE DATABASE` は既定では `template1` を複製する。`template1` は先ほどの `\l` の通り `en_US.utf8` で初期化済みであり、そこから `LC_COLLATE`/`LC_CTYPE` の異なるデータベースを複製しようとすると拒否される。 ```sql CREATE DATABASE postshop_bad ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'C'; ``` ```text ERROR: new collation (C) is incompatible with the collation of the template database (en_US.utf8) HINT: Use the same collation as in the template database, or use template0 as template. ``` `template0` はロケール中立なテンプレートで、ここを起点にすれば任意の `ENCODING`/`LC_COLLATE`/`LC_CTYPE` の組み合わせでデータベースを作成できる。 作成できたら `\l` で確認する。 ```text Name | Owner | Encoding | Locale Provider | Collate | Ctype | ... -----------+----------+----------+-----------------+------------+------------+----- postgres | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | ... postshop | postgres | UTF8 | libc | C | C | ... template0 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | ... template1 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | ... (4 rows) ``` `postshop` の `Collate`/`Ctype` が `C` になっていることを確認する。以降はこのデータベースに接続して作業する。 ```bash docker exec -it postshop-db psql -U postgres -d postshop ``` ### 共通スキーマを作成する 教材で使う EC ドメイン（PostShop）のスキーマは7テーブルで構成される。全13回を通してこのスキーマと列名・型を変えずに使うので、ここで一度に作る。金額は必ず `numeric`、日時は必ず `timestamptz` で持つ（理由は後述）。 ```sql -- 顧客（約 5万行） CREATE TABLE customers ( id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, email text NOT NULL UNIQUE, region text NOT NULL, -- '関東','近畿',... の8地域 created_at timestamptz NOT NULL DEFAULT now() ); -- カテゴリ（約 30行） CREATE TABLE categories ( id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name text NOT NULL UNIQUE ); -- 商品（約 5,000行）。price は「現在価格」 CREATE TABLE products ( id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, category_id bigint NOT NULL REFERENCES categories(id), name text NOT NULL, price numeric(10,2) NOT NULL CHECK (price >= 0) ); -- 価格履歴（約 2万行）。第6回で有効期間モデルとして使う CREATE TABLE product_prices ( id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, product_id bigint NOT NULL REFERENCES products(id), price numeric(10,2) NOT NULL CHECK (price >= 0), valid_from timestamptz NOT NULL, valid_to timestamptz -- NULL = 現在も有効 ); -- 第6回では tstzrange + EXCLUDE USING gist 版も別途提示する -- 注文（約 100万行）。本データセットの主役 CREATE TABLE orders ( id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, customer_id bigint NOT NULL REFERENCES customers(id), status text NOT NULL, -- pending/paid/shipped/delivered/cancelled/refunded ordered_at timestamptz NOT NULL ); -- 注文明細（約 300万行）。注文×商品の多対多の実体化 CREATE TABLE order_items ( order_id bigint NOT NULL REFERENCES orders(id), product_id bigint NOT NULL REFERENCES products(id), qty int NOT NULL CHECK (qty > 0), unit_price numeric(10,2) NOT NULL CHECK (unit_price >= 0), PRIMARY KEY (order_id, product_id) ); -- アクセスログ（約 1,000万行）。第13回で月次パーティション化する CREATE TABLE events ( id bigint GENERATED BY DEFAULT AS IDENTITY, customer_id bigint REFERENCES customers(id), event_type text NOT NULL, -- 'view'/'add_to_cart'/'purchase' 等 occurred_at timestamptz NOT NULL ); ``` テーブル間の関係を俯瞰しておく。 ```text customers ---< orders ---< order_items >--- products >--- categories | | +---< events +---< product_prices ``` `---<` は「1 対 多」を表し、鳥の足（`<`）が付いている側が「多」である。文章でも押さえておく。顧客（customers）は複数の注文（orders）と複数のアクセスログ（events）を持つ。カテゴリ（categories）は複数の商品（products）を分類する。商品は複数の価格履歴（product_prices）と複数の注文明細（order_items）を持つ。注文は複数の注文明細を持ち、注文明細は「注文×商品」の組を1行として表現する多対多の実体化テーブルである。 `\dt` で7テーブルができていることを、`\d orders` のようにして列・制約・インデックスを確認できる。 ```text Table "public.orders" Column | Type | Collation | Nullable | Default -------------+--------------------------+-----------+----------+---------------------------------- id | bigint | | not null | generated by default as identity customer_id | bigint | | not null | status | text | | not null | ordered_at | timestamp with time zone | | not null | Indexes: "orders_pkey" PRIMARY KEY, btree (id) Foreign-key constraints: "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id) Referenced by: TABLE "order_items" CONSTRAINT "order_items_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(id) ``` `\d` の出力にある `timestamp with time zone` が `timestamptz` の正式名称である。この段階では主キー（`PRIMARY KEY`）以外のインデックスは存在しない。検索を速くするインデックス設計は[第7回](07-storage-indexes-collation.md)で扱う。 ### generate_series で教材データを投入する 行数が少ないテーブルから順に投入する。`generate_series(1, N)` は 1 から N までの整数を1行ずつ返す集合返却関数で、`INSERT ... SELECT ... FROM generate_series(...)` と組み合わせると、for ループを書かずに大量の行を一括生成できる。乱数には `random()`（0以上1未満の一様乱数）、日時の分散には `now() - (random() * interval '365 days')`（過去365日以内のランダムな時刻）を使う。 ```sql -- customers: 5万件 INSERT INTO customers (email, region, created_at) SELECT 'customer' || g || '@example.com', (ARRAY['北海道','東北','関東','中部','関西','中国','四国','九州'])[1 + floor(random() * 8)], now() - (random() * interval '730 days') FROM generate_series(1, 50000) AS g; -- categories: 30件 INSERT INTO categories (name) SELECT 'カテゴリ' || g FROM generate_series(1, 30) AS g; -- products: 5,000件 INSERT INTO products (category_id, name, price) SELECT 1 + floor(random() * 30), '商品' || g, round((100 + random() * 19900)::numeric, 2) FROM generate_series(1, 5000) AS g; -- product_prices: 2万件 INSERT INTO product_prices (product_id, price, valid_from, valid_to) SELECT 1 + floor(random() * 5000), round((100 + random() * 19900)::numeric, 2), now() - (random() * interval '730 days'), NULL FROM generate_series(1, 20000) AS g; -- orders: 100万件 INSERT INTO orders (customer_id, status, ordered_at) SELECT 1 + floor(random() * 50000), (ARRAY['pending','paid','shipped','delivered','cancelled','refunded'])[1 + floor(random() * 6)], now() - (random() * interval '365 days') FROM generate_series(1, 1000000) AS g; -- order_items: 300万件 INSERT INTO order_items (order_id, product_id, qty, unit_price) SELECT 1 + floor(random() * 1000000), 1 + floor(random() * 5000), 1 + floor(random() * 5), round((100 + random() * 19900)::numeric, 2) FROM generate_series(1, 3000000) AS g ON CONFLICT (order_id, product_id) DO NOTHING; -- events: 1,000万件 INSERT INTO events (customer_id, event_type, occurred_at) SELECT 1 + floor(random() * 50000), (ARRAY['view','add_to_cart','purchase'])[1 + floor(random() * 3)], now() - (random() * interval '365 days') FROM generate_series(1, 10000000) AS g; ``` 2点補足する。第一に、`order_items` は `(order_id, product_id)` に複合主キーがあるため、乱数で選んだ組がまれに重複する。`ON CONFLICT (order_id, product_id) DO NOTHING` で重複行だけを無視するので、実際の投入行数は300万よりわずかに少なくなる（後述のハンズオンで確認する）。第二に、`order_id` と `product_id` はどちらも `SELECT` の対象リストに直接 `random()` を書くこと。もし「行ごとに変える乱数」のつもりで、外側の行を参照しないサブクエリ（`LATERAL` は、外側の行ごとに評価されるサブクエリを書くための句である）に `random()` を切り出すと、PostgreSQL がそのサブクエリを1回だけ評価して全行に使い回してしまい、生成される組がほぼ1種類に固定される場合がある。乱数は常に外側の `SELECT` リストに直接書くのが安全である。 `orders` は100万行、`order_items` は300万行、`events` は1,000万行と、この講座の中でも投入に時間がかかる部類のテーブルである。手元の検証環境（Docker Desktop、PostgreSQL 16）では、`orders` が数秒〜数十秒、`order_items` が数十秒、`events` が1分弱というオーダーだった（例。実行時間は環境に大きく依存するので、遅くても慌てず待つ）。`\timing on` にしておくと各 `INSERT` の所要時間が表示され、進み具合が分かる。 ```text INSERT 0 1000000 Time: 20044.724 ms (00:20.045) ``` ### なぜエンコーディング/照合順序は後から変えにくいのか `ENCODING`・`LC_COLLATE`・`LC_CTYPE`（または `ICU_LOCALE`）は、データベースを作成する瞬間に決まる属性であり、作成後に `ALTER DATABASE` で書き換えることはできない。 ```sql ALTER DATABASE postshop SET LC_COLLATE = 'ja-x-icu'; ``` ```text ERROR: unrecognized configuration parameter "lc_collate" ``` `lc_collate` はセッションの実行時パラメータ（GUC）ではなく、データベース作成時に固定される属性そのものなので、`SET` で変更する対象にすらならない。変更したい場合の唯一の道は、新しい設定でデータベースを作り直し、既存データを移し替えることである（`pg_dump` で出力し、新しいデータベースに `psql`/`pg_restore` で流し込む）。この移し替えには次のコストが伴う。 - 元のエンコーディングで格納されたテキストを、新しいエンコーディングとして解釈し直す必要がある。バイト列として不正な文字が混入していた場合はここで初めてエラーになる - 文字列を含む列に張られたインデックス（B-tree＝値をソート順に並べた多段の木。構造は[第7回](07-storage-indexes-collation.md)で扱う）は、作成時点の照合順序を前提に並んでいる。照合順序を変えると既存のインデックスは並び順の前提が崩れるため、索引を作り直す `REINDEX` が必要になる - テーブルが数百万〜数千万行あると、ダンプ・リストア・再インデックスはいずれも相応の時間がかかる本番作業になる PostgreSQL は `pg_collation` カタログに照合順序のバージョン情報（`collversion`）を記録しており、OS 側の glibc ロケールデータが更新されて記録済みバージョンとズレた場合に検知できる仕組みを持っている。裏を返せば、それだけ「照合順序が意図せず変わってインデックスと矛盾する」事故が実際に起こりうるということである。この一方通行性ゆえに、最初の `CREATE DATABASE` で `ENCODING`・`LC_COLLATE`・`LC_CTYPE` を意識して選ぶ価値がある。照合順序の実害（インデックスがどう壊れるか）は[第7回](07-storage-indexes-collation.md)で深掘りする。 ### timestamptz と timestamp、タイムゾーンの初期設定 共通スキーマの日時列はすべて `timestamptz`（`timestamp with time zone`）で宣言した。`timestamp`（タイムゾーン無し）との違いを整理する。 | | `timestamptz` | `timestamp` | |---|---|---| | 内部表現 | UTC の時刻として一意に保存される | タイムゾーン情報を持たない「壁時計の値」がそのまま保存される | | 入力時 | セッションの `timezone` 設定を使って UTC に変換してから保存 | 変換せずそのまま保存 | | 出力時 | セッションの `timezone` 設定に変換して表示 | そのまま表示 | | 表す対象 | 「地球上のある瞬間（instant）」 | 「どこかの壁時計が示す値」（どこの壁時計かは列自体には残らない） | `timestamptz` は常に UTC を基準にした「絶対時刻」として保存されるため、アプリケーションサーバーとデータベースのタイムゾーン設定が食い違っていても、`=`・`<`・`>` による比較や `ORDER BY` の結果は常に正しい。`timestamp` は保存時にどのタイムゾームだったかという情報が失われるため、後から「これは日本時間だったか UTC だったか」を突き合わせる作業が発生し、大抵は正しく復元できない。 セッションのタイムゾーン設定は `SHOW timezone;` で確認できる。公式 Docker イメージはコンテナの `TZ` 環境変数を指定しない限り既定で `Etc/UTC` になっている。 ```sql SHOW timezone; ``` ```text TimeZone ---------- Etc/UTC (1 row) ``` `now()` は常にこの設定に従って表示される（内部的には常に UTC で保持されている）。 ```sql SELECT now(); ``` ```text now ------------------------------- 2026-08-14 00:51:59.550867+00 (1 row) ``` 末尾の `+00` が UTC からのオフセットを表す。アプリケーション側で日本時間を表示したい場合は、保存形式を変えるのではなく、表示時に `AT TIME ZONE 'Asia/Tokyo'` のように変換する（詳しい型設計の判断基準は[第5回](05-datatypes-constraints.md)で扱う）。この講座では最初から一貫して `timestamptz` を使う方針を取り、`timestamp` は「避けるべき対比」としてのみ登場させる。 ## ハンズオン ### 課題1: 環境を作り、教材データを投入する やること: 本編の手順どおりに Docker コンテナを起動し、`postshop` データベースを `ENCODING`/`LC_COLLATE`/`LC_CTYPE` を明示して作成し、共通スキーマを作成し、7テーブルすべてにデータを投入する。完了したら、各テーブルの行数を1つのクエリで確認する。 **想定解答** ```sql SELECT 'customers' AS table_name, count(*) FROM customers UNION ALL SELECT 'categories', count(*) FROM categories UNION ALL SELECT 'products', count(*) FROM products UNION ALL SELECT 'product_prices', count(*) FROM product_prices UNION ALL SELECT 'orders', count(*) FROM orders UNION ALL SELECT 'order_items', count(*) FROM order_items UNION ALL SELECT 'events', count(*) FROM events; ``` 出力イメージ（実測例。`order_items` は `ON CONFLICT DO NOTHING` による間引きがあるため、実行ごとに300万よりわずかに少ない値になる）。 ```text table_name | count -----------------+---------- customers | 50000 categories | 30 products | 5000 product_prices | 20000 orders | 1000000 order_items | 2999138 events | 10000000 (7 rows) ``` なぜこれで解けるか: `UNION ALL` は複数の `SELECT` の結果を単純に縦に連結する集合演算で、各テーブルの `count(*)` を1回の実行で並べて見られる。`customers`・`categories`・`products`・`product_prices`・`orders`・`events` は一意制約に抵触しない生成方法なので指定した件数ちょうどになり、`order_items` だけが複合主キーの重複を `ON CONFLICT` で無視した分だけ少なくなる。 ### 課題2: 照合順序による並び順の違いを確認する やること: ひらがな・カタカナ・漢字・ラテン文字・数字を混ぜた小さなテーブルを作り、`COLLATE "C"` と `COLLATE "ja-x-icu"` で `ORDER BY` した結果を見比べる。 **想定解答** ```sql CREATE TABLE collation_demo (v text); INSERT INTO collation_demo (v) VALUES ('あ'), ('ア'), ('亜'), ('a'), ('Z'), ('012'); SELECT v FROM collation_demo ORDER BY v COLLATE "C"; ``` ```text v ----- 012 Z a あ ア 亜 (6 rows) ``` ```sql SELECT v FROM collation_demo ORDER BY v COLLATE "ja-x-icu"; ``` ```text v ----- 012 a Z あ ア 亜 (6 rows) ``` なぜこれで解けるか: `COLLATE "C"` はバイト値（UTF-8 ではおおよそ Unicode コードポイント値）でそのまま比較するため、`'Z'`（U+005A）は `'a'`（U+0061）よりバイト値が小さく先に来る。一方 `COLLATE "ja-x-icu"` は Unicode Collation Algorithm に基づく言語的な比較を行うため、ラテン文字は大文字・小文字を区別しない基準でまず `a` と `z` の並びとして比較され、`'a'` が `'Z'` より先に来る。数字は両方の照合順序で先頭に来ている点は共通だが、ラテン文字の大小関係が逆転していることが、`C` が「バイト順」、ICU が「言語ルール」という性質の違いをそのまま表している。この並び順の違いが検索・インデックスにどう影響するかは[第7回](07-storage-indexes-collation.md)で扱う。 ## よくあるつまずきと対処 | 症状 | 原因 | 対処 | |---|---|---| | 日本語を含む行を投入すると文字化けする、または `invalid byte sequence for encoding` エラーが出る | `ENCODING` を指定せずデータベースを作成し、環境依存で `SQL_ASCII` などマルチバイトを正しく扱わない符号化になっていた | `CREATE DATABASE` では常に `ENCODING 'UTF8'` を明示する。既に作ってしまった場合は `\l` で確認し、`TEMPLATE template0` から作り直す | | 集計や表示のタイムスタンプが数時間ずれる、日をまたいだ集計が合わない | `timestamp`（タイムゾーン無し）を選んでしまい、アプリケーションサーバーとデータベースの解釈がずれた | 日時列は原則 `timestamptz` にする。既存の `timestamp` 列は `ALTER TABLE ... ALTER COLUMN ... TYPE timestamptz USING 列 AT TIME ZONE '想定していたタイムゾーン'` で移行するが、"想定していたタイムゾーン" の特定自体が難しいことが多い | | `docker run` が `Bind for 0.0.0.0:5432 failed: port is already allocated.` で失敗する | ローカルに既存の PostgreSQL（Homebrew でインストール済みなど）や別のコンテナが既に `5432` を使っている | `lsof -i :5432`（macOS/Linux）で使用中のプロセスを確認して停止するか、`-p 5433:5432` のように別のホストポートに割り当て、以降の接続コマンドもポート番号を合わせる | ## 到達度チェックリスト - [ ] Docker で PostgreSQL 16 のコンテナを起動し、psql で接続できた - [ ] `ENCODING`/`LC_COLLATE`/`LC_CTYPE` を明示して `postshop` データベースを作成できた - [ ] `TEMPLATE template0` を付けずに作成すると失敗する理由を説明できる - [ ] 共通スキーマ7テーブルを作成し、`generate_series` で教材データを投入できた - [ ] `\l` でエンコーディングと `LC_COLLATE`/`LC_CTYPE` を確認できた - [ ] なぜ PostgreSQL で UTF-8 を使うのかを一言で説明できる - [ ] `C` 照合順序と `ja-x-icu` 照合順序で `ORDER BY` の結果が変わることを確認し、違いの理由を説明できる - [ ] `timestamptz` と `timestamp` の違いと、`timestamptz` を選ぶべき理由を説明できる ## 宿題（次回までの自習） 1. `collation_demo` テーブルに自分の名前や好きな単語をいくつか追加し、`COLLATE "ja-x-icu"` 以外の ICU 照合順序（`SELECT collname FROM pg_collation WHERE collname LIKE '%-x-icu';` で一覧を見て、たとえば `"en-x-icu"` や `"de-x-icu"`）でも `ORDER BY` を試し、並び順がどう変わるか観察する。 2. `\timing on` にした状態で `SELECT count(*) FROM customers;` と `SELECT count(*) FROM events;` をそれぞれ実行し、所要時間を比べてメモしておく。なぜ後者が遅いのか、自分なりの仮説を書き留めておく（答え合わせは[第8回](08-explain.md)で実行計画から行う）。 3. `orders` と `order_items` の `CREATE TABLE` を見返し、「なぜ `order_items` は `(order_id, product_id)` の複合主キーを持つのか」「なぜ `products.price`（現在価格）を直接参照せず、`order_items.unit_price` として金額を複製しているのか」を自分の言葉で仮説として書いておく。 ## 次回への接続 [第2回](02-relational-model.md)では、今回作った7テーブルを題材に「なぜテーブル・行・列という形でデータを表すのか」「キーと関数従属性とは何か」という関係モデルの理論を扱う。今回は手を動かしてスキーマを作ったが、次回はその設計判断の裏にある理屈を掘り下げる。照合順序が実際にインデックスやクエリ結果にどう効いてくるかは[第7回](07-storage-indexes-collation.md)で、`timestamp` 系の型設計の判断基準は[第5回](05-datatypes-constraints.md)で、それぞれ再訪する。 ## 参考 - PostgreSQL 公式チュートリアル: https://www.postgresql.org/docs/current/tutorial.html - Character Set Support: https://www.postgresql.org/docs/current/multibyte.html - Collation Support: https://www.postgresql.org/docs/current/collation.html - CREATE DATABASE: https://www.postgresql.org/docs/current/sql-createdatabase.html - Date/Time Types: https://www.postgresql.org/docs/current/datatype-datetime.html - Set Returning Functions（`generate_series` を含む）: https://www.postgresql.org/docs/current/functions-srf.html - Docker 公式 postgres イメージ: https://hub.docker.com/_/postgres