-- テーブル作成: メタデータをJSONBで持つ商品テーブル
CREATE TABLE products (
    id       SERIAL PRIMARY KEY,
    name     VARCHAR(200) NOT NULL,
    category VARCHAR(100) NOT NULL,
    price    DECIMAL(10, 2) NOT NULL,
    metadata JSONB NOT NULL DEFAULT '{}'
);
 
-- データ挿入: 商品ごとに異なる属性をJSONBに格納
INSERT INTO products (name, category, price, metadata) VALUES
('ThinkPad X1 Carbon', 'laptop', 198000, '{
    "brand": "Lenovo",
    "specs": {"cpu": "i7-1365U", "ram_gb": 16, "ssd_gb": 512},
    "tags": ["business", "lightweight", "14inch"],
    "weight_kg": 1.12,
    "ports": {"usb_c": 2, "usb_a": 2, "hdmi": 1}
}'),
('iPhone 15 Pro', 'smartphone', 159800, '{
    "brand": "Apple",
    "specs": {"chip": "A17 Pro", "ram_gb": 8, "storage_gb": 256},
    "tags": ["premium", "camera", "titanium"],
    "weight_g": 187,
    "colors": ["natural", "blue", "white", "black"]
}'),
('Pixel 8', 'smartphone', 82500, '{
    "brand": "Google",
    "specs": {"chip": "Tensor G3", "ram_gb": 8, "storage_gb": 128},
    "tags": ["ai", "camera", "affordable"],
    "weight_g": 187
}');
 
-- ===== フィールドアクセス演算子 =====
 
-- -> : JSONBオブジェクトを返す（ネスト参照に使う）
-- ->> : TEXT型で返す（最終的な値の取得に使う）
SELECT
    name,
    metadata->>'brand' AS brand,                    -- TEXT型で取得
    metadata->'specs'->>'cpu' AS cpu,                -- ネストしたキーの参照
    (metadata->'specs'->>'ram_gb')::INTEGER AS ram,  -- 型キャスト
    metadata->'tags'->0 AS first_tag,               -- 配列の0番目（JSONB型）
    metadata->'tags'->>0 AS first_tag_text,         -- 配列の0番目（TEXT型）
    metadata#>>'{specs,ram_gb}' AS ram_by_path       -- パス形式で取得
FROM products;
 
-- ===== JSONB の検索演算子 =====
 
-- @> : 包含（右辺のJSONが左辺に含まれるか）
SELECT * FROM products
WHERE metadata @> '{"brand": "Apple"}';
 
-- ? : キーの存在確認
SELECT * FROM products
WHERE metadata ? 'colors';
 
-- ?& : 全てのキーが存在するか
SELECT * FROM products
WHERE metadata ?& ARRAY['brand', 'specs', 'weight_g'];
 
-- ?| : いずれかのキーが存在するか
SELECT * FROM products
WHERE metadata ?| ARRAY['weight_kg', 'weight_g'];
 
-- ===== JSONB の更新 =====
 
-- || : マージ（既存キーは上書き、新規キーは追加）
UPDATE products
SET metadata = metadata || '{"warranty_years": 2, "in_stock": true}'
WHERE id = 1;
 
-- jsonb_set : 特定パスの値を更新
UPDATE products
SET metadata = jsonb_set(metadata, '{specs,ram_gb}', '32')
WHERE id = 1;
 
-- jsonb_set の第4引数 create_if_missing（デフォルトtrue）
UPDATE products
SET metadata = jsonb_set(metadata, '{specs,gpu}', '"RTX 4060"', true)
WHERE id = 1;
 
-- - : キーの削除
UPDATE products
SET metadata = metadata - 'warranty_years'
WHERE id = 1;
 
-- #- : ネストしたキーの削除（パス指定）
UPDATE products
SET metadata = metadata #- '{specs,gpu}'
WHERE id = 1;
 
-- ===== JSONB の集約 =====
 
-- ブランド別の商品数と平均価格
SELECT
    metadata->>'brand' AS brand,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products
GROUP BY metadata->>'brand'
ORDER BY avg_price DESC;

-- ===== GINインデックスの2つのオプション =====
 
-- 1. デフォルトGIN: @>, ?, ?|, ?& の全演算子に対応
CREATE INDEX idx_products_metadata
ON products USING GIN (metadata);
 
-- 2. jsonb_path_ops GIN: @> 演算子に特化（高速・省メモリ）
-- パスごとにハッシュ値を格納するため、包含検索が高速
CREATE INDEX idx_products_metadata_path
ON products USING GIN (metadata jsonb_path_ops);
 
-- WHY 2種類あるのか？
-- デフォルトGIN: キーの存在確認（?演算子）もインデックスで処理可能
-- jsonb_path_ops: @> 包含検索に特化、インデックスサイズが20-30%小さい
-- → アプリで使う演算子に応じて選択する
 
-- 3. 式インデックス: 特定フィールドのB-Tree（等値・範囲検索に最適）
CREATE INDEX idx_products_brand
ON products ((metadata->>'brand'));
 
-- 複合インデックス: ブランド + 価格帯の検索を高速化
CREATE INDEX idx_products_brand_price
ON products ((metadata->>'brand'), price);
 
-- ===== jsonpath クエリ（SQL:2016標準、PostgreSQL 12+）=====
 
-- @@ : jsonpathの条件式で検索
SELECT * FROM products
WHERE metadata @@ '$.specs.ram_gb > 8';
 
-- jsonb_path_query: パスに一致する値を全て返す
SELECT name, jsonb_path_query(metadata, '$.tags[*]') AS tag
FROM products;
 
-- jsonb_path_query_array: 結果を配列として返す
SELECT name, jsonb_path_query_array(metadata, '$.tags[*]') AS all_tags
FROM products;
 
-- jsonb_path_exists: パスが存在するか
SELECT name FROM products
WHERE jsonb_path_exists(metadata, '$.specs.cpu');
 
-- フィルタ付きjsonpath
SELECT name,
       jsonb_path_query(metadata, '$.tags[*] ? (@ like_regex "^cam")')
       AS camera_tag
FROM products;
 
-- ===== JSONB関数 =====
 
-- jsonb_each: キーと値のペアに展開
SELECT p.name, kv.key, kv.value
FROM products p,
     jsonb_each(p.metadata->'specs') AS kv(key, value)
WHERE p.id = 1;
 
-- jsonb_object_keys: キー一覧を取得
SELECT DISTINCT jsonb_object_keys(metadata) AS top_level_key
FROM products
ORDER BY top_level_key;
 
-- jsonb_array_elements: 配列の各要素に展開
SELECT p.name, tag.value AS tag
FROM products p,
     jsonb_array_elements_text(p.metadata->'tags') AS tag(value);
 
-- jsonb_strip_nulls: null値を含むキーを除去
SELECT jsonb_strip_nulls('{"a": 1, "b": null, "c": 3}'::JSONB);
-- → {"a": 1, "c": 3}
 
-- jsonb_typeof: 値の型を判定
SELECT jsonb_typeof(metadata->'specs') AS specs_type,
       jsonb_typeof(metadata->'tags') AS tags_type,
       jsonb_typeof(metadata->'weight_kg') AS weight_type
FROM products WHERE id = 1;
-- → object, array, number
 
-- jsonb_build_object / jsonb_build_array: JSONBの動的構築
SELECT jsonb_build_object(
    'product_name', name,
    'brand', metadata->>'brand',
    'price', price
) AS summary
FROM products;

-- PostgreSQL 17+ の JSON Schema バリデーション（将来対応）
-- 現時点ではCHECK制約とIS JSON述語で代用する
 
-- IS JSON述語（PostgreSQL 16+）
ALTER TABLE products
ADD CONSTRAINT check_metadata_is_object
CHECK (metadata IS JSON OBJECT);
 
-- CHECK制約でJSONBの構造を検証
ALTER TABLE products
ADD CONSTRAINT check_metadata_has_brand
CHECK (metadata ? 'brand');
 
-- CHECK制約でJSONBの値を検証
ALTER TABLE products
ADD CONSTRAINT check_metadata_brand_not_empty
CHECK (length(metadata->>'brand') > 0);
 
-- トリガーによる高度なバリデーション
CREATE OR REPLACE FUNCTION validate_product_metadata()
RETURNS TRIGGER AS $$
BEGIN
    -- brandフィールドは必須
    IF NOT (NEW.metadata ? 'brand') THEN
        RAISE EXCEPTION 'metadata must contain "brand" key';
    END IF;
 
    -- specsフィールドがある場合はオブジェクトであること
    IF NEW.metadata ? 'specs'
       AND jsonb_typeof(NEW.metadata->'specs') != 'object' THEN
        RAISE EXCEPTION 'metadata.specs must be an object';
    END IF;
 
    -- tagsフィールドがある場合は配列であること
    IF NEW.metadata ? 'tags'
       AND jsonb_typeof(NEW.metadata->'tags') != 'array' THEN
        RAISE EXCEPTION 'metadata.tags must be an array';
    END IF;
 
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_validate_product_metadata
    BEFORE INSERT OR UPDATE ON products
    FOR EACH ROW EXECUTE FUNCTION validate_product_metadata();

-- ===== テーブル作成 =====
CREATE TABLE blog_posts (
    id         SERIAL PRIMARY KEY,
    title      VARCHAR(200) NOT NULL,
    body       TEXT,
    tags       TEXT[] NOT NULL DEFAULT '{}',
    scores     INTEGER[] DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- ===== データ挿入（2つの構文）=====
INSERT INTO blog_posts (title, body, tags, scores) VALUES
('PostgreSQL入門', 'PostgreSQLの基礎を解説します',
 ARRAY['database', 'postgresql', 'sql'],              -- ARRAY構文
 ARRAY[85, 92, 78]),
('React入門', 'Reactの基礎を解説します',
 '{"frontend", "react", "javascript"}',                -- リテラル構文
 '{90, 88, 95}'),
('TypeScript実践', 'TypeScriptの型システムについて',
 ARRAY['frontend', 'typescript', 'javascript'],
 ARRAY[95, 91, 89]);
 
-- ===== 配列演算子 =====
 
-- @> : 左辺が右辺を含むか（包含）
SELECT * FROM blog_posts WHERE tags @> ARRAY['postgresql'];
 
-- <@ : 左辺が右辺に含まれるか
SELECT * FROM blog_posts WHERE tags <@ ARRAY['database', 'postgresql', 'sql', 'mysql'];
 
-- && : 共通要素があるか（重複チェック）
SELECT * FROM blog_posts WHERE tags && ARRAY['react', 'sql'];
 
-- = ANY() : いずれかの要素に一致
SELECT * FROM blog_posts WHERE 'javascript' = ANY(tags);
 
-- インデックスアクセス（1始まり！PostgreSQLの配列は1-indexed）
SELECT title, tags[1] AS first_tag, tags[2] AS second_tag
FROM blog_posts;
 
-- スライス
SELECT title, tags[1:2] AS first_two_tags FROM blog_posts;
 
-- ===== 配列関数 =====
 
-- array_length: 配列の長さ（第2引数は次元、通常1）
SELECT title, array_length(tags, 1) AS tag_count FROM blog_posts;
 
-- array_to_string: 配列を文字列に結合
SELECT title, array_to_string(tags, ', ') AS tag_list FROM blog_posts;
 
-- array_position: 要素の位置を返す（なければNULL）
SELECT title, array_position(tags, 'javascript') AS js_pos FROM blog_posts;
 
-- array_remove: 特定の要素を除去した新しい配列を返す
SELECT title, array_remove(tags, 'javascript') AS tags_no_js FROM blog_posts;
 
-- array_cat: 配列の結合
SELECT array_cat(ARRAY[1,2,3], ARRAY[4,5,6]);  -- → {1,2,3,4,5,6}
 
-- array_append / array_prepend: 要素の追加
UPDATE blog_posts
SET tags = array_append(tags, 'tutorial')
WHERE id = 1;
 
UPDATE blog_posts
SET tags = array_prepend('featured', tags)
WHERE id = 1;
 
-- ===== UNNEST: 配列の展開（最重要関数）=====
 
-- 全記事のタグを行に展開
SELECT p.title, t.tag
FROM blog_posts p, UNNEST(p.tags) AS t(tag);
 
-- タグの出現回数を集計
SELECT tag, COUNT(*) AS usage_count
FROM blog_posts, UNNEST(tags) AS tag
GROUP BY tag
ORDER BY usage_count DESC;
 
-- 全タグの重複排除リスト（ソート済み）
SELECT ARRAY_AGG(DISTINCT tag ORDER BY tag) AS all_tags
FROM blog_posts, UNNEST(tags) AS tag;
 
-- スコアの統計
SELECT
    title,
    array_length(scores, 1) AS num_scores,
    (SELECT AVG(s) FROM UNNEST(scores) AS s) AS avg_score,
    (SELECT MAX(s) FROM UNNEST(scores) AS s) AS max_score,
    (SELECT MIN(s) FROM UNNEST(scores) AS s) AS min_score
FROM blog_posts;
 
-- ===== 配列用GINインデックス =====
CREATE INDEX idx_posts_tags ON blog_posts USING GIN (tags);
 
-- インデックス使用確認
EXPLAIN ANALYZE
SELECT * FROM blog_posts WHERE tags @> ARRAY['postgresql'];
-- → Bitmap Index Scan on idx_posts_tags（GINインデックスが使われる）

-- ===== 範囲型の種類 =====
-- INT4RANGE  : 整数範囲
-- INT8RANGE  : bigint範囲
-- NUMRANGE   : 数値範囲
-- DATERANGE  : 日付範囲
-- TSRANGE    : タイムスタンプ範囲（タイムゾーンなし）
-- TSTZRANGE  : タイムスタンプ範囲（タイムゾーンあり）
 
-- ===== 予約システム: 部屋の重複チェック =====
CREATE EXTENSION IF NOT EXISTS btree_gist;  -- EXCLUDE制約に必要
 
CREATE TABLE room_reservations (
    id       SERIAL PRIMARY KEY,
    room_id  INTEGER NOT NULL,
    period   DATERANGE NOT NULL,
    guest    VARCHAR(100) NOT NULL,
    -- EXCLUDE制約: 同じ部屋で期間が重複する予約を禁止
    EXCLUDE USING GIST (room_id WITH =, period WITH &&)
);
 
-- 正常な予約
INSERT INTO room_reservations (room_id, period, guest) VALUES
(101, '[2024-03-01, 2024-03-05)', '田中太郎'),   -- 3/1〜3/4
(101, '[2024-03-05, 2024-03-08)', '鈴木一郎'),   -- 3/5〜3/7（隣接はOK）
(102, '[2024-03-01, 2024-03-05)', '佐藤花子');   -- 別の部屋はOK
 
-- エラーになる予約（重複）
-- INSERT INTO room_reservations (room_id, period, guest) VALUES
-- (101, '[2024-03-04, 2024-03-06)', '山田次郎');
-- → ERROR: conflicting key value violates exclusion constraint
 
-- ===== 範囲演算子 =====
 
-- @> : 範囲が値を含むか
SELECT * FROM room_reservations
WHERE period @> '2024-03-03'::DATE;
 
-- && : 範囲同士が重複するか
SELECT * FROM room_reservations
WHERE period && '[2024-03-03, 2024-03-06)'::DATERANGE;
 
-- << : 左辺が右辺より完全に前か
-- >> : 左辺が右辺より完全に後か
-- -|- : 隣接しているか
 
-- 範囲の演算
SELECT
    '[2024-03-01, 2024-03-05)'::DATERANGE
    * '[2024-03-03, 2024-03-08)'::DATERANGE AS intersection,  -- 共通部分
    '[2024-03-01, 2024-03-05)'::DATERANGE
    + '[2024-03-03, 2024-03-08)'::DATERANGE AS union_range;    -- 和集合
 
-- lower/upper: 範囲の下限・上限を取得
SELECT
    guest,
    lower(period) AS check_in,
    upper(period) AS check_out,
    upper(period) - lower(period) AS stay_days
FROM room_reservations;
 
-- ===== 勤務シフト管理 =====
CREATE TABLE work_shifts (
    id          SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL,
    shift_time  TSTZRANGE NOT NULL,
    EXCLUDE USING GIST (employee_id WITH =, shift_time WITH &&)
);
 
INSERT INTO work_shifts (employee_id, shift_time) VALUES
(1, '[2024-03-01 09:00, 2024-03-01 17:00)'::TSTZRANGE),
(1, '[2024-03-01 18:00, 2024-03-01 22:00)'::TSTZRANGE);  -- 隣接しないのでOK
 
-- ===== 価格帯テーブル =====
CREATE TABLE price_tiers (
    id          SERIAL PRIMARY KEY,
    tier_name   VARCHAR(50) NOT NULL,
    quantity    INT4RANGE NOT NULL,
    unit_price  DECIMAL(10, 2) NOT NULL,
    EXCLUDE USING GIST (quantity WITH &&)  -- 数量範囲の重複禁止
);
 
INSERT INTO price_tiers (tier_name, quantity, unit_price) VALUES
('個人', '[1, 10)', 1000),
('小口', '[10, 100)', 800),
('大口', '[100, 1000)', 600),
('卸売', '[1000,)', 400);  -- 上限なし
 
-- 数量に応じた単価を取得
SELECT tier_name, unit_price
FROM price_tiers
WHERE quantity @> 50;  -- → '小口', 800

-- ===== tsvector / tsquery ベースの全文検索 =====
 
CREATE TABLE articles (
    id            SERIAL PRIMARY KEY,
    title         VARCHAR(300) NOT NULL,
    body          TEXT NOT NULL,
    search_vector TSVECTOR,
    created_at    TIMESTAMPTZ DEFAULT NOW()
);
 
-- search_vectorをトリガーで自動更新
CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.body, '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_update_search_vector
    BEFORE INSERT OR UPDATE OF title, body ON articles
    FOR EACH ROW EXECUTE FUNCTION update_search_vector();
 
-- GINインデックス
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector);
 
-- データ挿入（トリガーでsearch_vectorが自動設定される）
INSERT INTO articles (title, body) VALUES
('PostgreSQL Full-Text Search Guide',
 'PostgreSQL provides built-in full-text search using tsvector and tsquery. It supports stemming, ranking, and highlighting.'),
('Index Optimization Techniques',
 'Understanding B-Tree, GIN, and GiST indexes is essential for PostgreSQL performance tuning.'),
('Database Security Best Practices',
 'Implementing row-level security, encryption, and audit logging protects your PostgreSQL database.');
 
-- ===== 検索クエリ =====
 
-- 基本検索
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles,
     to_tsquery('english', 'postgresql & index') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;
 
-- フレーズ検索（PostgreSQL 13+）
SELECT title
FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'full text search');
 
-- OR検索
SELECT title FROM articles
WHERE search_vector @@ to_tsquery('english', 'security | encryption');
 
-- NOT検索（securityを含むがencryptionを含まない）
SELECT title FROM articles
WHERE search_vector @@ to_tsquery('english', 'security & !encryption');
 
-- ハイライト表示
SELECT
    title,
    ts_headline('english', body,
        to_tsquery('english', 'postgresql'),
        'StartSel=<b>, StopSel=</b>, MaxWords=35, MinWords=15'
    ) AS highlighted
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql');
 
-- ===== pg_trgm: トライグラム（あいまい検索）=====
 
CREATE EXTENSION IF NOT EXISTS pg_trgm;
 
-- トライグラムの確認
SELECT show_trgm('PostgreSQL');
-- → {"  p"," po","gre","osq","pos","ostg","pgr","que","res","sql","stg","tgr"}
 
-- トライグラムGINインデックス
CREATE INDEX idx_articles_title_trgm
ON articles USING GIN (title gin_trgm_ops);
 
-- 類似度検索（タイポ許容）
SELECT title, similarity(title, 'Postgre') AS sim
FROM articles
WHERE title % 'Postgre'  -- デフォルト閾値 0.3 以上
ORDER BY sim DESC;
 
-- 類似度の閾値変更
SET pg_trgm.similarity_threshold = 0.1;
 
-- LIKE/ILIKEでもトライグラムインデックスが使える
EXPLAIN ANALYZE
SELECT * FROM articles WHERE title ILIKE '%security%';
-- → Bitmap Index Scan on idx_articles_title_trgm
 
-- ===== 日本語全文検索の課題と対策 =====
-- to_tsvectorのデフォルトパーサーは空白区切りのため、
-- 日本語のように空白なしの言語は対応が必要
 
-- 方式1: pg_bigm拡張（バイグラムベース）
-- CREATE EXTENSION IF NOT EXISTS pg_bigm;
-- CREATE INDEX idx_title_bigm ON articles USING GIN (title gin_bigm_ops);
-- SELECT * FROM articles WHERE title LIKE '%データベース%';
 
-- 方式2: pgroonga拡張（MeCab形態素解析ベース）
-- CREATE EXTENSION IF NOT EXISTS pgroonga;
-- CREATE INDEX idx_articles_pgroonga ON articles USING pgroonga (title, body);
-- SELECT * FROM articles WHERE title &@~ 'データベース';
 
-- 方式3: simpleパーサー + LIKE（小規模データ向け）
SELECT * FROM articles
WHERE to_tsvector('simple', title) @@ to_tsquery('simple', 'postgresql');

-- ===== INET / CIDR / MACADDR 型 =====
 
CREATE TABLE access_logs (
    id          BIGSERIAL PRIMARY KEY,
    client_ip   INET NOT NULL,
    subnet      CIDR,
    mac_address MACADDR,
    accessed_at TIMESTAMPTZ DEFAULT NOW(),
    path        VARCHAR(500)
);
 
INSERT INTO access_logs (client_ip, subnet, path) VALUES
('192.168.1.100', '192.168.1.0/24', '/api/users'),
('10.0.0.50', '10.0.0.0/8', '/api/orders'),
('192.168.1.200', '192.168.1.0/24', '/api/products');
 
-- サブネットに含まれるか
SELECT * FROM access_logs
WHERE client_ip << '192.168.1.0/24';
 
-- サブネットに含まれるか（等しいも含む）
SELECT * FROM access_logs
WHERE client_ip <<= '192.168.0.0/16';
 
-- IPアドレスのホスト部分を取得
SELECT client_ip, host(client_ip), masklen(subnet)
FROM access_logs;
 
-- GiSTインデックスでIP範囲検索を高速化
CREATE INDEX idx_access_logs_ip ON access_logs USING GIST (client_ip inet_ops);

-- ===== 利用可能な拡張の確認 =====
SELECT name, default_version, comment
FROM pg_available_extensions
WHERE comment IS NOT NULL
ORDER BY name
LIMIT 20;
 
-- インストール済み拡張の確認
SELECT extname, extversion FROM pg_extension ORDER BY extname;
 
-- ===== uuid-ossp: UUID生成 =====
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
 
SELECT uuid_generate_v4();                    -- ランダムUUID
SELECT uuid_generate_v1();                    -- タイムスタンプベースUUID
SELECT gen_random_uuid();                     -- PostgreSQL 13+標準関数
 
-- UUIDを主キーとして使う
CREATE TABLE users_v2 (
    id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name  VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);
 
-- ===== pgcrypto: 暗号化 =====
CREATE EXTENSION IF NOT EXISTS pgcrypto;
 
-- パスワードハッシュ（bcrypt）
SELECT crypt('my_password', gen_salt('bf', 12));
-- → $2a$12$xxxx...（60文字のbcryptハッシュ）
 
-- パスワード検証
SELECT (crypt('my_password', stored_hash) = stored_hash) AS is_valid;
 
-- 対称鍵暗号（AES-256）
SELECT pgp_sym_encrypt('機密データ', 'encryption_key');
SELECT pgp_sym_decrypt(
    pgp_sym_encrypt('機密データ', 'encryption_key'),
    'encryption_key'
);
 
-- ランダムバイト列
SELECT encode(gen_random_bytes(32), 'hex') AS random_token;
 
-- ===== pg_trgm: あいまい検索 =====
CREATE EXTENSION IF NOT EXISTS pg_trgm;
 
CREATE TABLE customers (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL
);
 
INSERT INTO customers (name) VALUES
('田中太郎'), ('田中太朗'), ('田仲太郎'), ('鈴木一郎');
 
CREATE INDEX idx_customers_name_trgm ON customers USING GIN (name gin_trgm_ops);
 
-- 類似度検索
SELECT name, similarity(name, '田中太郎') AS sim
FROM customers
ORDER BY sim DESC;
-- → 田中太郎: 1.0, 田中太朗: 0.75, 田仲太郎: 0.5, 鈴木一郎: 0.0
 
-- ===== hstore: 簡易キーバリューストア =====
CREATE EXTENSION IF NOT EXISTS hstore;
 
CREATE TABLE app_settings (
    user_id  INTEGER PRIMARY KEY,
    prefs    HSTORE
);
 
INSERT INTO app_settings VALUES
(1, 'theme => dark, lang => ja, font_size => 14'),
(2, 'theme => light, lang => en');
 
SELECT user_id, prefs->'theme' AS theme, prefs->'lang' AS lang
FROM app_settings;
 
-- ※ 現在はJSONBの方が推奨。hstoreはネストできない
 
-- ===== PostGIS: 地理空間（別途インストールが必要）=====
-- CREATE EXTENSION IF NOT EXISTS postgis;
--
-- CREATE TABLE shops (
--     id       SERIAL PRIMARY KEY,
--     name     VARCHAR(200),
--     location GEOMETRY(Point, 4326)
-- );
--
-- INSERT INTO shops (name, location) VALUES
-- ('東京本店', ST_SetSRID(ST_MakePoint(139.7671, 35.6812), 4326)),
-- ('大阪支店', ST_SetSRID(ST_MakePoint(135.5023, 34.6937), 4326));
--
-- -- 東京駅から5km以内の店舗
-- SELECT name, ST_Distance(
--     location::geography,
--     ST_SetSRID(ST_MakePoint(139.7671, 35.6812), 4326)::geography
-- ) AS distance_m
-- FROM shops
-- WHERE ST_DWithin(
--     location::geography,
--     ST_SetSRID(ST_MakePoint(139.7671, 35.6812), 4326)::geography,
--     5000
-- );
 
-- ===== pg_stat_statements: クエリ統計（本番環境では必須）=====
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
-- トップ10の遅いクエリ
SELECT
    query,
    calls,
    round(total_exec_time::NUMERIC, 2) AS total_ms,
    round(mean_exec_time::NUMERIC, 2) AS avg_ms,
    rows,
    round((100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0))::NUMERIC, 2)
        AS cache_hit_pct
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
 
-- ===== pgvector: ベクトル類似度検索（AI/ML向け）=====
-- CREATE EXTENSION IF NOT EXISTS vector;
--
-- CREATE TABLE document_embeddings (
--     id        SERIAL PRIMARY KEY,
--     content   TEXT NOT NULL,
--     embedding vector(1536)  -- OpenAI text-embedding-3-small の次元数
-- );
--
-- CREATE INDEX ON document_embeddings
--     USING ivfflat (embedding vector_cosine_ops)
--     WITH (lists = 100);
--
-- -- コサイン類似度で最近傍検索
-- SELECT content, 1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
-- FROM document_embeddings
-- ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
-- LIMIT 5;

-- ===== GENERATED COLUMNS（計算列、PostgreSQL 12+）=====
 
CREATE TABLE order_items (
    id          SERIAL PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    quantity    INTEGER NOT NULL CHECK (quantity > 0),
    unit_price  DECIMAL(10, 2) NOT NULL CHECK (unit_price > 0),
    tax_rate    DECIMAL(5, 4) NOT NULL DEFAULT 0.10,
    -- STORED: ディスクに保存される計算列
    subtotal    DECIMAL(12, 2) GENERATED ALWAYS AS
                (quantity * unit_price) STORED,
    tax_amount  DECIMAL(12, 2) GENERATED ALWAYS AS
                (quantity * unit_price * tax_rate) STORED,
    total       DECIMAL(12, 2) GENERATED ALWAYS AS
                (quantity * unit_price * (1 + tax_rate)) STORED
);
 
INSERT INTO order_items (product_name, quantity, unit_price) VALUES
('ノートPC', 2, 150000),
('マウス', 5, 3000);
 
SELECT product_name, quantity, unit_price, subtotal, tax_amount, total
FROM order_items;
-- ノートPC | 2 | 150000 | 300000 | 30000 | 330000
-- マウス   | 5 | 3000   | 15000  | 1500  | 16500
 
-- WHY GENERATED COLUMNS？
-- → アプリケーション側で計算不要（バグ防止）
-- → 計算値にインデックスを張れる
-- → STORED列は物理的に保存されるのでSELECT時の計算コストゼロ
-- → VIRTUAL列（PostgreSQL未対応、MySQL対応）はSELECT時に計算
 
-- ===== LISTEN / NOTIFY（リアルタイム通知）=====
 
-- セッション1（リスナー）:
LISTEN order_created;
LISTEN order_status_changed;
 
-- セッション2（通知側）:
NOTIFY order_created, '{"order_id": 42, "amount": 5000}';
 
-- トリガーで自動通知する実践例
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status      VARCHAR(20) NOT NULL DEFAULT 'pending',
    total       DECIMAL(12, 2) NOT NULL,
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);
 
CREATE OR REPLACE FUNCTION notify_order_event()
RETURNS TRIGGER AS $$
DECLARE
    payload JSONB;
BEGIN
    IF TG_OP = 'INSERT' THEN
        payload := jsonb_build_object(
            'event', 'order_created',
            'order_id', NEW.id,
            'customer_id', NEW.customer_id,
            'total', NEW.total
        );
        PERFORM pg_notify('order_events', payload::TEXT);
    ELSIF TG_OP = 'UPDATE' AND OLD.status != NEW.status THEN
        payload := jsonb_build_object(
            'event', 'order_status_changed',
            'order_id', NEW.id,
            'old_status', OLD.status,
            'new_status', NEW.status
        );
        PERFORM pg_notify('order_events', payload::TEXT);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_notify_order
    AFTER INSERT OR UPDATE ON orders
    FOR EACH ROW EXECUTE FUNCTION notify_order_event();
 
-- WHY LISTEN/NOTIFY？
-- → ポーリング不要のリアルタイム通知
-- → 外部メッセージキューが不要な軽量なイベント通知
-- → トランザクションに連動（COMMITされた変更のみ通知される）
-- 制限:
-- → ペイロードは8KB以内
-- → 永続化されない（リスナーが未接続なら通知は失われる）
-- → 大規模イベント処理にはKafka/RabbitMQが適切
 
-- ===== テーブル継承（Table Inheritance）=====
 
-- 基底テーブル
CREATE TABLE events (
    id         BIGSERIAL PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    user_id    INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- 子テーブル（基底テーブルのカラムを継承）
CREATE TABLE click_events (
    element_id  VARCHAR(100),
    page_url    VARCHAR(500)
) INHERITS (events);
 
CREATE TABLE purchase_events (
    product_id  INTEGER NOT NULL,
    amount      DECIMAL(10, 2) NOT NULL,
    currency    VARCHAR(3) DEFAULT 'JPY'
) INHERITS (events);
 
-- 子テーブルへの挿入
INSERT INTO click_events (event_type, user_id, element_id, page_url)
VALUES ('click', 1, 'btn-submit', '/checkout');
 
INSERT INTO purchase_events (event_type, user_id, product_id, amount)
VALUES ('purchase', 1, 42, 5000);
 
-- 親テーブルを検索すると全子テーブルのデータも返る
SELECT * FROM events;
 
-- 親テーブルのみ検索（子テーブルを除外）
SELECT * FROM ONLY events;
 
-- WHY テーブル継承？
-- → 共通カラムの定義を一箇所に集約
-- → 親テーブルへのクエリで全子テーブルを検索可能
-- 注意:
-- → 現在はパーティショニングの方が推奨（PostgreSQL 10+）
-- → 継承ではUNIQUE制約やFKが子テーブルに適用されない
-- → 新規プロジェクトではDECLARATIVE PARTITIONINGを使うこと

-- NG: 本来テーブルで管理すべきデータを全てJSONBに
CREATE TABLE bad_orders (
    id   SERIAL PRIMARY KEY,
    data JSONB  -- 顧客情報、商品情報、配送先、全てJSONに
);
 
INSERT INTO bad_orders (data) VALUES ('{
    "customer": {"name": "田中", "email": "tanaka@example.com"},
    "items": [{"product": "PC", "price": 150000}],
    "shipping": {"address": "東京都...", "method": "express"}
}');
 
-- 問題点:
-- 1. 外部キー制約が効かない → データ整合性を保証できない
-- 2. JOINできない → 集計・分析が困難
-- 3. 型安全性がない → 数値が文字列として入るバグ
-- 4. スキーマの自己文書化機能が失われる
-- 5. NOT NULL制約・CHECK制約が使えない
 
-- OK: リレーショナルデータはテーブル、可変部分のみJSONB
CREATE TABLE good_orders (
    id          SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    status      VARCHAR(20) NOT NULL DEFAULT 'pending',
    total       DECIMAL(12,2) NOT NULL,
    metadata    JSONB DEFAULT '{}'  -- メモ、タグ、外部APIレスポンス等の可変情報のみ
);
 
-- 判断基準:
-- ・WHERE句で頻繁に検索する → テーブルカラム
-- ・外部キー制約が必要 → テーブルカラム
-- ・スキーマが固定 → テーブルカラム
-- ・スキーマが可変/任意 → JSONB
-- ・表示のみで検索しない → JSONB

-- NG: 配列でM:N関係を表現
CREATE TABLE bad_articles (
    id       SERIAL PRIMARY KEY,
    title    VARCHAR(200),
    tag_ids  INTEGER[]  -- FK制約が効かない！
);
 
-- 問題点:
-- 1. tag_ids内の値が実在するtagsレコードか検証できない
-- 2. tags.idが削除されても配列内の参照は残る（孤立参照）
-- 3. タグの付帯情報（追加日時等）を持てない
-- 4. タグの変更時にUPDATEが配列操作になり複雑
-- 5. パフォーマンス: JOINの最適化が効かない
 
-- OK: 中間テーブルでM:N関係を表現
CREATE TABLE tags (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
);
 
CREATE TABLE good_articles (
    id    SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL
);
 
CREATE TABLE article_tags (
    article_id INTEGER REFERENCES good_articles(id) ON DELETE CASCADE,
    tag_id     INTEGER REFERENCES tags(id) ON DELETE CASCADE,
    added_at   TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (article_id, tag_id)
);
 
-- 外部キー制約、カスケード削除、JOIN最適化が全て可能
 
-- ただし、タグが単なる文字列ラベルで
-- 外部キーが不要な場合は配列型がシンプルで適切
CREATE TABLE simple_articles (
    id    SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    tags  TEXT[] NOT NULL DEFAULT '{}'  -- 文字列タグのリスト
);
-- tags用にGINインデックスを張れば検索も高速
CREATE INDEX idx_simple_articles_tags
ON simple_articles USING GIN (tags);

-- NG: GINインデックスなしでJSONB検索
SELECT * FROM products
WHERE metadata @> '{"brand": "Apple"}';
-- → Seq Scan: テーブル全体をスキャンしてJSONBの中身を比較
 
-- OK: GINインデックスを作成してから検索
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
-- または特定のブランドで頻繁に検索するなら式インデックス
CREATE INDEX idx_products_brand ON products ((metadata->>'brand'));
-- → Index Scan に変わり、大幅に高速化
 
-- EXPLAIN ANALYZEでインデックスの利用を確認する習慣をつける
EXPLAIN ANALYZE
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';