PostgreSQL入門：PostgreSQLとは何か、そしてなぜそれを選ぶのか

-- PostgreSQL：複数のユースケースを1つのデータベースで

-- 1. 従来のリレーショナル検索
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

-- 2. JSONBの柔軟なストレージ（MongoDB不要）
INSERT INTO products (name, attributes)
VALUES ('ランニングシューズ', '{"color": "red", "size": 42, "tags": ["sport", "outdoor"]}'::jsonb);

-- 3. 全文検索（Elasticsearch不要）
SELECT name FROM products
WHERE to_tsvector('english', name) @@ to_tsquery('english', 'running & shoes');

-- PostgreSQL：INSERT ON CONFLICT（より柔軟）
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = NOW()
RETURNING id, name, updated_at;
-- RETURNING句は影響を受けた行を返す（MySQLには同等のものがない）

INSERT 0 1

-- MySQL：ON DUPLICATE KEY UPDATE（柔軟性が低い）
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name), updated_at = NOW();
-- RETURNING句がないため、結果を得るには別途SELECTが必要

-- PostgreSQL：GINインデックス付きJSONB（高速なインデックス検索）
SELECT name FROM products
WHERE attributes @> '{"color": "red"}'::jsonb;
-- @> は「含む」演算子、GINインデックスを使用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- MySQL：JSON関数（このパターンではインデックスサポートなし）
SELECT name FROM products
WHERE JSON_CONTAINS(attributes, '"red"', '$.color');
-- インデックスなし、▶ルテーブルスキャン

-- PostgreSQL：ランキング付き組み込み全文検索
SELECT name, ts_rank(to_tsvector('english', name || ' ' || description),
                     to_tsquery('english', 'red & shoes')) AS rank
FROM products
WHERE to_tsvector('english', name || ' ' || description)
      @@ to_tsquery('english', 'red & shoes')
ORDER BY rank DESC;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- MySQL：基本的なFULLTEXT検索（ランキングの柔軟性なし）
SELECT name FROM products
WHERE MATCH(name, description) AGAINST('red shoes' IN BOOLEAN MODE);

-- 利用可能な拡張を確認
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE name IN ('uuid-ossp', 'pg_trgm', 'pgcrypto');

-- 拡張をインストール
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 拡張を使ってUUIDを生成
SELECT uuid_generate_v4();
-- 出力：550e8400-e29b-41d4-a716-446655440000 のような一意のUUID

CREATE TABLE

-- PostgreSQL：条件付き集計のためのFILTER句（SQL標準）
SELECT
    store_id,
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders,
    COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_orders
FROM orders
GROUP BY store_id;

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

-- MySQL：SUM(IF()) または SUM(CASE) の回避策が必要
SELECT
    store_id,
    COUNT(*) AS total_orders,
    SUM(IF(status = 'completed', 1, 0)) AS completed_orders,
    SUM(IF(status = 'cancelled', 1, 0)) AS cancelled_orders
FROM orders
GROUP BY store_id;

-- 商品ごとに異なる属性がある場合
-- 属性タイプごとに個別のテーブルや列は不要

INSERT INTO products (name, price, attributes) VALUES
    ('ランニングシューズ', 89.99, '{"color": "red", "size": 42, "weight_grams": 280}'::jsonb),
    ('ノートパソコン', 1299.00, '{"cpu": "M3", "ram_gb": 16, "screen_inch": 14}'::jsonb),
    ('コーヒー豆', 24.50, '{"origin": "Colombia", "roast": "medium", "weight_kg": 1}'::jsonb);

-- クエリ：100ドル未満の赤色の商品をすべて検索
SELECT name, price, attributes
FROM products
WHERE price < 100
  AND attributes @> '{"color": "red"}'::jsonb;

     name      | price  |                 attributes
---------------+--------+--------------------------------------------
 ランニングシューズ |  89.99 | {"color": "red", "size": 42, "weight_grams": 280}

-- ============================================
-- 総合的な例：PostgreSQLの機能デモ
-- PGの1つのデータベースが複数のツールを置き換えられる理由を示す
-- ============================================

-- 1. ACIDトランザクション（アプリケーション層の整合性処理が不要）
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;

-- 2. JSONBストレージ（MongoDB不要）
INSERT INTO products (name, attributes)
VALUES ('スマートウォッチ', '{"color": "black", "battery_life_hours": 48, "waterproof": true}'::jsonb);

-- 3. 全文検索（Elasticsearch不要）
SELECT name, ts_rank(
    to_tsvector('english', name),
    plainto_tsquery('english', 'smart watch')
) AS relevance
FROM products
WHERE to_tsvector('english', name) @@ plainto_tsquery('english', 'smart watch')
ORDER BY relevance DESC;

-- 4. ウィンドウ関数（アプリケーション層のランキング処理が不要）
SELECT name, price,
    RANK() OVER (ORDER BY price DESC) AS price_rank
FROM products;

-- 5. UPSERT（アプリケーション層の競合処理が不要）
INSERT INTO products (name, price, attributes)
VALUES ('スマートウォッチ', 199.99, '{"color": "black", "battery_life_hours": 48}'::jsonb)
ON CONFLICT (name)
DO UPDATE SET price = EXCLUDED.price,
              attributes = EXCLUDED.attributes
RETURNING id, name, price;

 -- UPSERT RETURNING の出力：
 id |    name     | price
----+-------------+--------
  1 | スマートウォッチ | 199.99