PostgreSQL入門 - リレーショナルデータベースの基礎

このチュートリアルで学ぶこと

✓ PostgreSQLのセットアップ
✓ 基本的なSQL文（SELECT, INSERT, UPDATE, DELETE）
✓ テーブルの作成とリレーション
✓ インデックスとパフォーマンス
✓ トランザクション
✓ 実践的なクエリパターン

前提条件

• コマンドラインの基本操作
• 基本的なプログラミング知識

Step 1: PostgreSQLのセットアップ

インストール

# macOS (Homebrew)
brew install postgresql@16
brew services start postgresql@16

# Ubuntu/Debian
sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql

# Windows
# PostgreSQL公式サイトからインストーラーをダウンロード

接続確認

# PostgreSQLに接続
psql -U postgres

# バージョン確認
SELECT version();

# 終了
\q

データベースの作成

-- データベース作成
CREATE DATABASE tutorial_db;

-- データベース一覧
\l

-- データベースに接続
\c tutorial_db

-- 現在のデータベース確認
SELECT current_database();

Step 2: テーブルの作成と基本操作

テーブル作成

-- ユーザーテーブル
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 商品テーブル
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 注文テーブル
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    ordered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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

テーブル確認

-- テーブル一覧
\dt

-- テーブル構造確認
\d users
\d+ products

-- 詳細情報
SELECT
    column_name,
    data_type,
    character_maximum_length,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_name = 'users';

Step 3: データの挿入（INSERT）

基本的なINSERT

-- 単一行の挿入
INSERT INTO users (username, email, password_hash, full_name)
VALUES ('tanaka', 'tanaka@example.com', 'hash123', '田中太郎');

-- 複数行の挿入
INSERT INTO users (username, email, password_hash, full_name)
VALUES
    ('yamada', 'yamada@example.com', 'hash456', '山田花子'),
    ('suzuki', 'suzuki@example.com', 'hash789', '鈴木一郎'),
    ('sato', 'sato@example.com', 'hash012', '佐藤美咲');

-- 商品データの挿入
INSERT INTO products (name, description, price, stock, category)
VALUES
    ('ノートPC', '高性能ノートパソコン', 98000, 50, 'electronics'),
    ('マウス', 'ワイヤレスマウス', 3500, 200, 'electronics'),
    ('キーボード', 'メカニカルキーボード', 12000, 80, 'electronics'),
    ('Tシャツ', 'コットン100%', 2500, 150, 'clothing'),
    ('デニムパンツ', 'スリムフィット', 8000, 60, 'clothing');

RETURNING句

-- 挿入したデータを取得
INSERT INTO users (username, email, password_hash)
VALUES ('newuser', 'newuser@example.com', 'newhash')
RETURNING id, username, created_at;

Step 4: データの取得（SELECT）

基本的なSELECT

-- 全カラム取得
SELECT * FROM users;

-- 特定カラムの取得
SELECT id, username, email FROM users;

-- 条件付き取得
SELECT * FROM users WHERE is_active = true;

-- 複数条件
SELECT * FROM products
WHERE category = 'electronics' AND price < 10000;

-- OR条件
SELECT * FROM products
WHERE category = 'electronics' OR category = 'clothing';

-- IN句
SELECT * FROM products
WHERE category IN ('electronics', 'clothing');

-- LIKE（部分一致）
SELECT * FROM users WHERE email LIKE '%@example.com';

-- NULL判定
SELECT * FROM users WHERE full_name IS NOT NULL;

ソートと制限

-- ソート（昇順）
SELECT * FROM products ORDER BY price ASC;

-- ソート（降順）
SELECT * FROM products ORDER BY price DESC;

-- 複数カラムでソート
SELECT * FROM products ORDER BY category ASC, price DESC;

-- 件数制限
SELECT * FROM products LIMIT 10;

-- オフセット付き（ページネーション）
SELECT * FROM products
ORDER BY id
LIMIT 10 OFFSET 20;  -- 21件目から10件

集計関数

-- 件数
SELECT COUNT(*) FROM users;

-- 合計
SELECT SUM(price * stock) as total_value FROM products;

-- 平均
SELECT AVG(price) as average_price FROM products;

-- 最大・最小
SELECT MAX(price), MIN(price) FROM products;

-- グループ化
SELECT category, COUNT(*) as count, AVG(price) as avg_price
FROM products
GROUP BY category;

-- HAVING（グループの絞り込み）
SELECT category, COUNT(*) as count
FROM products
GROUP BY category
HAVING COUNT(*) >= 2;

Step 5: データの更新と削除

UPDATE

-- 単一行の更新
UPDATE users
SET full_name = '田中太郎（更新）', updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

-- 複数行の更新
UPDATE products
SET price = price * 0.9  -- 10%オフ
WHERE category = 'electronics';

-- RETURNING句
UPDATE products
SET stock = stock - 1
WHERE id = 1
RETURNING id, name, stock;

DELETE

-- 条件付き削除
DELETE FROM users WHERE id = 5;

-- 複数行削除
DELETE FROM products WHERE stock = 0;

-- 全件削除（注意）
-- DELETE FROM products;

-- TRUNCATEで高速削除
-- TRUNCATE TABLE products;

Step 6: JOINによるテーブル結合

テストデータの準備

-- 注文データ
INSERT INTO orders (user_id, total_amount, status)
VALUES
    (1, 101500, 'completed'),
    (2, 15500, 'pending'),
    (1, 8000, 'completed');

-- 注文明細データ
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
    (1, 1, 1, 98000),
    (1, 2, 1, 3500),
    (2, 2, 2, 3500),
    (2, 3, 1, 12000),
    (3, 5, 1, 8000);

INNER JOIN

-- ユーザーと注文を結合
SELECT
    u.username,
    o.id as order_id,
    o.total_amount,
    o.status,
    o.ordered_at
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

LEFT JOIN

-- 全ユーザーと注文（注文がないユーザーも含む）
SELECT
    u.username,
    COUNT(o.id) as order_count,
    COALESCE(SUM(o.total_amount), 0) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

複数テーブルの結合

-- 注文詳細（ユーザー、注文、商品を結合）
SELECT
    u.username,
    o.id as order_id,
    p.name as product_name,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) as subtotal
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.id
INNER JOIN users u ON o.user_id = u.id
INNER JOIN products p ON oi.product_id = p.id
ORDER BY o.id, oi.id;

Step 7: サブクエリとCTE

サブクエリ

-- WHEREでのサブクエリ
SELECT * FROM users
WHERE id IN (
    SELECT DISTINCT user_id FROM orders
);

-- 平均以上の価格の商品
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- FROM句でのサブクエリ
SELECT category, avg_price
FROM (
    SELECT category, AVG(price) as avg_price
    FROM products
    GROUP BY category
) as category_stats
WHERE avg_price > 5000;

CTE（Common Table Expression）

-- WITH句を使った可読性の高いクエリ
WITH user_orders AS (
    SELECT
        user_id,
        COUNT(*) as order_count,
        SUM(total_amount) as total_amount
    FROM orders
    GROUP BY user_id
)
SELECT
    u.username,
    u.email,
    uo.order_count,
    uo.total_amount
FROM users u
INNER JOIN user_orders uo ON u.id = uo.user_id
ORDER BY uo.total_amount DESC;

-- 再帰CTE（階層データ）
WITH RECURSIVE category_tree AS (
    -- ベースケース
    SELECT id, name, parent_id, 0 as level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 再帰ケース
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

Step 8: インデックス

インデックスの作成

-- 単一カラムのインデックス
CREATE INDEX idx_products_category ON products(category);

-- 複合インデックス
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- ユニークインデックス
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 部分インデックス
CREATE INDEX idx_active_users ON users(username) WHERE is_active = true;

インデックスの確認

-- テーブルのインデックス一覧
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'products';

-- クエリの実行計画確認
EXPLAIN SELECT * FROM products WHERE category = 'electronics';

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

Step 9: トランザクション

基本的なトランザクション

-- トランザクション開始
BEGIN;

-- 在庫を減らして注文を作成
UPDATE products SET stock = stock - 1 WHERE id = 1;
INSERT INTO orders (user_id, total_amount) VALUES (1, 98000);

-- 問題がなければコミット
COMMIT;

-- 問題があればロールバック
-- ROLLBACK;

トランザクション分離レベル

-- 分離レベルの設定
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- クエリ実行
SELECT * FROM products WHERE id = 1 FOR UPDATE;
UPDATE products SET stock = stock - 1 WHERE id = 1;

COMMIT;

SAVEPOINT

BEGIN;

INSERT INTO users (username, email, password_hash)
VALUES ('test1', 'test1@example.com', 'hash');

SAVEPOINT sp1;

INSERT INTO users (username, email, password_hash)
VALUES ('test2', 'test2@example.com', 'hash');

-- test2の挿入だけ取り消し
ROLLBACK TO sp1;

-- test1はコミット
COMMIT;

Step 10: 実践的なクエリパターン

ページネーション

-- オフセットベース
SELECT * FROM products
ORDER BY id
LIMIT 20 OFFSET 40;  -- 3ページ目

-- カーソルベース（推奨）
SELECT * FROM products
WHERE id > 40
ORDER BY id
LIMIT 20;

全文検索

-- 全文検索インデックス
CREATE INDEX idx_products_search
ON products
USING GIN (to_tsvector('japanese', name || ' ' || COALESCE(description, '')));

-- 検索クエリ
SELECT * FROM products
WHERE to_tsvector('japanese', name || ' ' || COALESCE(description, ''))
    @@ to_tsquery('japanese', 'ノート');

ウィンドウ関数

-- ランキング
SELECT
    name,
    category,
    price,
    RANK() OVER (ORDER BY price DESC) as overall_rank,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) as category_rank
FROM products;

-- 累計
SELECT
    ordered_at::date as order_date,
    total_amount,
    SUM(total_amount) OVER (ORDER BY ordered_at) as running_total
FROM orders;

UPSERT（INSERT ON CONFLICT）

-- 存在すれば更新、なければ挿入
INSERT INTO products (id, name, price, stock)
VALUES (1, 'ノートPC Pro', 120000, 30)
ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock;

ベストプラクティス

1. インデックス設計
   - WHERE句やJOINで使うカラムにインデックス
   - 更新が多いテーブルは控えめに
   - EXPLAINで効果を確認

2. クエリ最適化
   - SELECT * は避ける
   - 必要なカラムのみ取得
   - N+1問題を避けてJOINを使用

3. トランザクション
   - 短く保つ
   - 適切な分離レベルを選択
   - デッドロックに注意

4. セキュリティ
   - パラメータ化クエリを使用
   - 最小権限の原則
   - 機密データは暗号化

まとめ

PostgreSQLは強力なリレーショナルデータベースです。基本的なCRUD操作からJOIN、トランザクションまでマスターすれば、多くのアプリケーションのデータ管理に対応できます。パフォーマンスを意識したインデックス設計も重要です。