PostgreSQL: PostgreSQL総合基礎演習

Q: JSONB metadata内のyearフィールドの型は何ですか？

JSONB内の数値はデフォルトでnumericです。抽出するには ->>'year' （TEXTを返す）を使用し、その後 ::INTEGER でキャストします。yearで頻繁に検索する場合は、GENERATEDカラムまたは式インデックスを追加してください。

最終更新：2026-08-26

最初の6つの基礎レッスンを終えたら、すべてを1つの完全なプロジェクトにまとめる時です。この記事では、オンライン書店データベースをゼロから構築する手順を解説します。

1. 学習内容

要件分析からテーブル設計までの完全なフロー
データベースの作成と接続の切り替え
制約付きの複数関連テーブルの設計
一括挿入とUPSERTデータインポート
データの正確性を確認する単純なクエリ
テーブル構造の変更とクリーンアップ

2. 個人開発者の実話

(1) 課題: 一人でデータベースを構築

Aliceは個人開発者で、オンライン書店プロジェクトのデータベースをセットアップする必要があります。要件は以下の通りです:

5つのコアテーブル: users、books、categories、orders、reviews
ユーザーのメールアドレスは一意。パスワードは空欄不可
書籍の価格は0より大きい。ISBNは一意
注文はユーザーと書籍をリンク
レビューには評価（1〜5）が必要
初期データを一括インポートする必要がある。一部の書籍は既に存在する可能性あり（UPSERTが必要）

(2) 完全なデータベース構築フロー

Aliceは以下の手順でゼロからデータベース初期化を完了します:

SQL

-- ステップ1: データベースを作成
CREATE DATABASE bookstore;

-- ステップ2: 新しいデータベースに接続
\c bookstore

-- ステップ3〜7: 適切な型と制約でテーブルを作成
-- （完全なSQLは以下のセクション6に記載）

(3) 成果

完全なデータベース設計経験（要件からSQLまで）
5テーブル + 制約 + 外部キーカスケード = 本番レベルの設計
UPSERT一括インポート = 実践的なスキル
30分で完了 = 一人で完遂できる能力

3. 要件分析

(1) オンライン書店ER図

erDiagram
    USERS ||--o{ ORDERS : "注文する"
    BOOKS ||--o{ ORDER_ITEMS : "含む"
    CATEGORIES ||--o{ BOOKS : "持つ"
    USERS ||--o{ REVIEWS : "書く"
    BOOKS ||--o{ REVIEWS : "受ける"
    ORDERS ||--|| ORDER_ITEMS : "含む"

    USERS {
        int id PK
        varchar email UK
        varchar name
        varchar password_hash
        timestamptz created_at
    }
    CATEGORIES {
        int id PK
        varchar name UK
        text description
    }
    BOOKS {
        int id PK
        varchar isbn UK
        varchar title
        decimal price
        int stock
        int category_id FK
        jsonb metadata
    }
    ORDERS {
        int id PK
        int user_id FK
        varchar status
        decimal total
        timestamptz created_at
    }
    ORDER_ITEMS {
        int id PK
        int order_id FK
        int book_id FK
        int quantity
        decimal unit_price
    }
    REVIEWS {
        int id PK
        int user_id FK
        int book_id FK
        smallint rating
        text content
        timestamptz created_at
    }

(2) テーブルと制約一覧

テーブル	カラム数	主要制約
users	5	email UNIQUE NOT NULL, password_hash NOT NULL
categories	3	name UNIQUE
books	7	isbn UNIQUE, price > 0 CHECK, stock >= 0 CHECK, category_id FK
orders	5	user_id FK CASCADE, ステータス CHECK, total >= 0 CHECK
order_items	5	order_id FK CASCADE, book_id FK RESTRICT, quantity > 0 CHECK
reviews	6	user_id FK SET NULL, book_id FK CASCADE, rating 1-5 CHECK

4. ステップバイステップで構築

(1) データベースの作成と接続

▶ サンプル: 書店データベースの作成

SQL

-- 書店データベースを作成
CREATE DATABASE bookstore
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8';

-- 新しいデータベースに接続
\c bookstore

TEXT 📖 参照専用

CREATE TABLE

(2) カテゴリテーブルの作成

▶ サンプル: categoriesテーブル

SQL

-- カテゴリテーブル: 書籍のジャンル
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT
);

-- 初期カテゴリを挿入
INSERT INTO categories (name, description) VALUES
    ('Programming', 'ソフトウェア開発とプログラミング言語に関する書籍'),
    ('Database', 'データベース設計、SQL、データ管理'),
    ('Data Science', '機械学習、統計、データ分析'),
    ('DevOps', 'CI/CD、クラウドコンピューティング、インフラストラクチャ'),
    ('Web Development', 'フロントエンドとバックエンドのWeb技術');

TEXT 📖 参照専用

INSERT 0 1

(3) ユーザーテーブルの作成

▶ サンプル: usersテーブル

SQL

-- ユーザーテーブル: 書店の顧客
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    password_hash CHAR(60) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- サンプルユーザーを挿入
INSERT INTO users (email, name, password_hash) VALUES
    ('alice@example.com', 'Alice', '$2a$12$hash_alice_1234567890abcdefghijklmnopqrs'),
    ('bob@example.com', 'Bob', '$2a$12$hash_bob_1234567890abcdefghijklmnopqrstuvwx'),
    ('charlie@example.com', 'Charlie', '$2a$12$hash_charlie_1234567890abcdefghijklmno')
RETURNING id, email, name;

TEXT 📖 参照専用

INSERT 0 1

(4) 書籍テーブルの作成

▶ サンプル: booksテーブル

SQL

-- 書籍テーブル: コア商品
CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    isbn VARCHAR(13) UNIQUE NOT NULL,
    title VARCHAR(300) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    metadata JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- メタデータ付きでサンプル書籍を挿入
INSERT INTO books (isbn, title, price, stock, category_id, metadata) VALUES
    ('9780134685991', 'Effective Python', 39.99, 120, 1,
     '{"author": "Brett Slatkin", "pages": 352, "edition": 2}'::jsonb),
    ('9780596007270', 'Learning PostgreSQL', 44.99, 80, 2,
     '{"author": "Regina Obe", "pages": 500}'::jsonb),
    ('9781491910368', 'Python Data Science Handbook', 49.99, 60, 3,
     '{"author": "Jake VanderPlas", "pages": 548}'::jsonb),
    ('9781098118283', 'Kubernetes Up and Running', 54.99, 45, 4,
     '{"author": "Brendan Burns", "pages": 300, "edition": 3}'::jsonb),
    ('9781718500417', 'CSS in Depth', 42.99, 90, 5,
     '{"author": "Keith Grant", "pages": 432}'::jsonb)
RETURNING id, title, price;

TEXT 📖 参照専用

INSERT 0 1

(5) 注文テーブルと注文明細テーブルの作成

▶ サンプル: orders + order_itemsテーブル

SQL

-- 注文テーブル
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status VARCHAR(20) DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    total_amount DECIMAL(12, 2) DEFAULT 0 CHECK (total_amount >= 0),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 注文明細テーブル
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL CHECK (unit_price > 0)
);

-- サンプル注文を挿入
INSERT INTO orders (user_id, status, total_amount) VALUES
    (1, 'paid', 84.98)
RETURNING id;

-- 注文明細を挿入（注文id = 1と仮定）
INSERT INTO order_items (order_id, book_id, quantity, unit_price) VALUES
    (1, 1, 1, 39.99),
    (1, 2, 1, 44.99);

TEXT 📖 参照専用

INSERT 0 1

(6) レビューテーブルの作成

▶ サンプル: reviewsテーブル

SQL

-- レビューテーブル: 書籍に対するユーザーレビュー
CREATE TABLE reviews (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE CASCADE,
    rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    title VARCHAR(200),
    content TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- サンプルレビューを挿入
INSERT INTO reviews (user_id, book_id, rating, title, content) VALUES
    (1, 1, 5, '素晴らしいPythonのヒント',
     'この本は日常業務で使える実践的なPythonのヒントを多数カバーしてい��す。'),
    (2, 2, 4, 'PostgreSQL入門として良い',
     '初心者には最適ですが、パーティショニングなどの応用トピックがもっと欲しいです。'),
    (3, 3, 5, 'データサイエンティスト必携',
     'NumPy、Pandas、Matplotlib、Scikit-learnの包括的なカバレッジ。')
RETURNING id, rating, title;

TEXT 📖 参照専用

INSERT 0 1

5. UPSERT一括インポート

▶ サンプル: 書籍の一括インポート（一部は既に存在）

SQL

-- 新しい書籍のバッチをインポート: 一部は既に存在（isbnで判別）、一部は新規
INSERT INTO books (isbn, title, price, stock, category_id, metadata) VALUES
    ('9780134685991', 'Effective Python', 35.99, 150, 1,
     '{"author": "Brett Slatkin", "pages": 352, "edition": 2}'::jsonb),
    ('9780596007270', 'Learning PostgreSQL', 49.99, 100, 2,
     '{"author": "Regina Obe", "pages": 500}'::jsonb),
    ('9781119557265', 'SQL for Data Analysis', 34.99, 200, 2,
     '{"author": "Ulka Rodgers", "pages": 288}'::jsonb),
    ('9781484254555', 'PostgreSQL High Performance', 59.99, 30, 2,
     '{"author": "Ibragimov", "pages": 400}'::jsonb)
ON CONFLICT (isbn)
DO UPDATE SET
    price = EXCLUDED.price,
    stock = books.stock + EXCLUDED.stock
RETURNING id, title,
    CASE WHEN xmax = 0 THEN 'NEW' ELSE 'UPDATED' END AS operation;

TEXT 📖 参照専用

 id |            title             | operation
----+------------------------------+-----------
  1 | Effective Python             | UPDATED
  2 | Learning PostgreSQL          | UPDATED
  6 | SQL for Data Analysis        | NEW
  7 | PostgreSQL High Performance  | NEW

6. 検証クエリ

▶ サンプル: データ整合性の検証

SQL

-- 1. 各テーブルのレコード数をカウント
SELECT 'users' AS table_name, COUNT(*) FROM users
UNION ALL SELECT 'categories', COUNT(*) FROM categories
UNION ALL SELECT 'books', COUNT(*) FROM books
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items
UNION ALL SELECT 'reviews', COUNT(*) FROM reviews;

-- 2. 外部キーリレーションシップを確認
SELECT o.id AS order_id, u.name AS customer,
       b.title, oi.quantity, oi.unit_price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN books b ON oi.book_id = b.id;

-- 3. 制約が機能することを確認（失敗するはず）
-- INSERT INTO books (isbn, title, price, stock) VALUES ('test', 'Test', -10, 5);
-- エラー: check constraint "books_price_check" 違反

-- INSERT INTO reviews (user_id, book_id, rating) VALUES (1, 1, 6);
-- エラー: check constraint "reviews_rating_check" 違反

-- 4. UPSERT結果を確認
SELECT isbn, title, price, stock
FROM books
WHERE isbn IN ('9780134685991', '9781119557265')
ORDER BY isbn;

TEXT 📖 参照専用

 count 
-------
     5
(1 row)

7. テーブル構造の変更

▶ サンプル: ALTER TABLEによる反復的な改善

SQL

-- booksにpublished_yearカラムを追加
ALTER TABLE books ADD COLUMN published_year INTEGER;

-- booksにis_featuredフラグを追加
ALTER TABLE books ADD COLUMN is_featured BOOLEAN DEFAULT false;

-- metadata JSONBからpublished_yearを更新
UPDATE books
SET published_year = (metadata->>'year')::INTEGER
WHERE metadata ? 'year';

-- usersにphoneカラムを追加
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 変更を確認
\d books
\d users

TEXT 📖 参照専用

-- SQL statement executed successfully

8. 総合サンプル: ワンクリック初期化ス��リプト

SQL

-- ============================================
-- 総合サンプル: 書店データベース初期化スクリプト
-- postgres DBに接続したpsqlから実行
-- ============================================

-- 1. データベースを作成
CREATE DATABASE bookstore
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8';

-- 2. 新しいデータベースに接続
\c bookstore

-- 3. 依存順に全テーブルを作成
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    password_hash CHAR(60) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    isbn VARCHAR(13) UNIQUE NOT NULL,
    title VARCHAR(300) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    metadata JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status VARCHAR(20) DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    total_amount DECIMAL(12, 2) DEFAULT 0 CHECK (total_amount >= 0),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL CHECK (unit_price > 0)
);

CREATE TABLE reviews (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE CASCADE,
    rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    title VARCHAR(200),
    content TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 4. サンプルデータを挿入
INSERT INTO categories (name, description) VALUES
    ('Programming', 'ソフトウェア開発とプログラミング言語'),
    ('Database', 'データベース設計、SQL、データ管理'),
    ('Data Science', '機械学習とデータ分析');

INSERT INTO users (email, name, password_hash) VALUES
    ('alice@example.com', 'Alice', '$2a$12$hash_alice'),
    ('bob@example.com', 'Bob', '$2a$12$hash_bob');

INSERT INTO books (isbn, title, price, stock, category_id, metadata) VALUES
    ('9780134685991', 'Effective Python', 39.99, 120, 1,
     '{"author": "Brett Slatkin", "pages": 352}'::jsonb),
    ('9780596007270', 'Learning PostgreSQL', 44.99, 80, 2,
     '{"author": "Regina Obe", "pages": 500}'::jsonb),
    ('9781491910368', 'Python Data Science Handbook', 49.99, 60, 3,
     '{"author": "Jake VanderPlas", "pages": 548}'::jsonb);

INSERT INTO orders (user_id, total_amount) VALUES (1, 84.98);
INSERT INTO order_items (order_id, book_id, quantity, unit_price) VALUES
    (1, 1, 1, 39.99), (1, 2, 1, 44.99);

INSERT INTO reviews (user_id, book_id, rating, title, content) VALUES
    (1, 1, 5, '素晴らしい', '実践的なPythonのヒント。'),
    (2, 2, 4, '良い入門書', '初心者に最適。');

-- 5. 確認
SELECT 'categories' AS t, COUNT(*) FROM categories
UNION ALL SELECT 'users', COUNT(*) FROM users
UNION ALL SELECT 'books', COUNT(*) FROM books
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'reviews', COUNT(*) FROM reviews;

TEXT 📖 参照専用

     t      | count
------------+-------
 categories |     3
 users      |     2
 books      |     3
 orders     |     1
 reviews    |     2

❓ よくある質問

Q テーブルの作成順序は重要ですか？

A はい、重要です。外部キーが参照するテーブルは先に存在している必要があるためです。正しい順序は: 参照されるテーブルを先に作成し（categories、users）、次にそれらを参照するテー��ル（books、orders、reviews）、最後に中間テーブル（order_items）です。

Q ON DELETE CASCADEとRESTRICTの選び方は？

A 親行の削除に伴って子行も削除すべき場合はCASCADEを使用します（例: ユーザー削除時に関連注文も削除）。子行が存在する場合は親を削除できないようにする場合はRESTRICTを使用します（例: 注文明細で参照されている書籍は削除不可）。一般的なルール: ビジネス上カスケード削除が許容される場合はCASCADE、許容されない場合はRESTRICTを使用します。

Q UPSERTでのxmax = 0は何を意味しますか？

A xmaxはPostgreSQLのシステムカラムで、その行に対する削除/更新のトランザクションIDを記録します。INSERTではxmaxを設定しません（値は0）。UPDATE/DELETEでは設定します。つまり、xmax = 0は行が新規挿入されたことを意味し、xmax != 0は更新さ▶たことを意味します。これはPostgreSQL内部のテクニックです。

Q 一時テーブル（TEMP TABLE）と通常テーブルの違いは何ですか？

A 一時テーブルは現在のセッション内でのみ表示され、セッション終了時に自動的に削除されます。中間のインポートデータや一時的な計算結果の保存に適しています。利点: 公開名前空間を汚染せず、手動クリーンアップが不要です。

Q JSONB metadata内のyearフィールドの型は何ですか？

A JSONB内の数値はデフォルトでnumericです。抽出するには->>'year'（TEXTを返す）を使用し、その後::INTEGERでキャストします。yearで頻繁に検索する場合は、GENERATEDカラムまたは式インデックスを追加してください。

Q この書店データベースは本番環境で使用できますか？

A 構造的には使用可能ですが、本番環境に必要な要素がまだ不足しています: 1) インデックスチューニング（後のレッスン）。2) 監査フィールド（updated_at）。3) 論理削除（deleted_at）。4) データベースユーザー権限（非root接続）。5) バックアップ戦略。後のレッスンでこれらを順次追加していきます。

📖 まとめ

データベース設計フロー: 要件分析 → ER図 → 作成順序 → 制約設計 → データインポート → 検証
テーブル作成順序は外部キー依存関係に従う: 参照されるテーブルを先に、参照するテーブルを後に
制約はデータ品質を保護: UNIQUE（一意性）/ CHECK（条件検証）/ FK（参照整合性）/ NOT NULL（非空）
UPSERT一括インポート: ON CONFLICT DO UPDATEで重複データを処理
RETURNING句で操作結果を確認
ALTER TABLEでテーブル構造を反復的に改善（カラム追加、制約変更）
総合クエリでデータ整合性を確認: JOINでテーブル結合 + COUNTで統計

📝 練習問題

基本（★）: このレッスンの手順に従って、書店データベースと全6テーブルをゼロから作成し、サンプルデータを挿入して、検証クエリを実行して各テーブルの行数が例と一致することを確認してください。
中級（★★）: 書店データベースでUPSERTを実行: 4件の書籍レコードをインポートします（うち2件は既存のISBN）。既存書籍は価格を更新して在庫を増やし、新規書籍は通常挿入します。RETURNINGで各レコードがNEWかUPDATEDかを表示してください。
チャレンジ（★★★）: 書店データベースにbook_authors多対多中間テーブル（1冊の本に複数の著者、1人の著者が複数の本）とauthorsテーブルを追加してください。テーブル構造を設計し（外部キーと制約付き）、3人の著者と中間データを挿入して、各書籍の全著者名を表示するJOINクエリを記述してください。

Web-Tutorial 技術チーム

複数の開発者によって共同維持されているプログラミングチュートリアルプラットフォーム。各チュートリアルは専門分野の開発者が執筆・レビューしています。正確で信頼性の高いコンテンツを目指しています — 問題を見つけた場合はお知らせください。

私たちについて