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', '高性能ノートパソコン', 70000, 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件

集計関数はデータ分析の基本です。GROUP BYと組み合わせることで、カテゴリごとの統計情報を取得できます。

集計関数

-- 件数
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, 70000),
 (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, 70000);

-- 問題がなければコミット
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、トランザクションまでマスターすれば、多くのアプリケーションのデータ管理に対応できます。パフォーマンスを意識したインデックス設計も重要です。

参考リソース

• PostgreSQL 公式ドキュメント
• PostgreSQL Tutorial
• SQL コマンドリファレンス
• psql コマンド

×

PR

×

PR