DDL 與 Schema 管理：PostgreSQL 資料結構定義與分區策略 | PostgreSQL

-- 建立表格後，查看系統目錄紀錄
CREATE TABLE orders (
    order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL,
    total_amount numeric(12, 2)
);

-- 查看 pg_class 中的紀錄
SELECT relname, relkind, relnatts, relhaspkey
FROM pg_class
WHERE relname = 'orders';
-- relkind: 'r'=普通表格, 'i'=索引, 'v'=視圖, 'p'=分區父表

-- 查看欄位資訊
SELECT attname, atttypid::regtype, attnum, attnotnull
FROM pg_attribute
WHERE attrelid = 'orders'::regclass
  AND attnum > 0
ORDER BY attnum;

-- 查看 Constraint 資訊
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'orders'::regclass;

-- 基本建表
CREATE TABLE IF NOT EXISTS products (
    product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku        varchar(50)    NOT NULL UNIQUE,
    name       text           NOT NULL,
    price      numeric(10, 2) NOT NULL CHECK (price >= 0),
    category_id int           REFERENCES categories(category_id) ON DELETE SET NULL,
    is_active  boolean        NOT NULL DEFAULT true,
    metadata   jsonb,
    created_at timestamptz    NOT NULL DEFAULT now(),
    updated_at timestamptz    NOT NULL DEFAULT now()
);

-- 從查詢結果建立表格（含資料）
CREATE TABLE orders_archive AS
SELECT * FROM orders WHERE created_at < '2024-01-01';

-- 指定儲存參數
CREATE TABLE high_write_table (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    data text
) WITH (fillfactor = 70);  -- 留 30% 空間給 UPDATE（HOT 優化）

-- 新增欄位（PG11+ 有預設值幾乎即時完成，無需重寫表格）
ALTER TABLE products ADD COLUMN in_stock boolean NOT NULL DEFAULT true;

-- 刪除欄位
ALTER TABLE products DROP COLUMN weight_kg;

-- 修改欄位型別（需確認相容性，可能重寫表格）
ALTER TABLE products ALTER COLUMN name TYPE varchar(200);

-- 重命名欄位
ALTER TABLE products RENAME COLUMN sku TO product_code;

-- 設定/移除 NOT NULL
ALTER TABLE products ALTER COLUMN metadata SET NOT NULL;
ALTER TABLE products ALTER COLUMN metadata DROP NOT NULL;

-- 安全刪除
DROP TABLE IF EXISTS temp_import;

-- 級聯刪除（同時刪除依賴物件）
DROP TABLE IF EXISTS categories CASCADE;

-- 清空表格（比 DELETE 快，重置序列）
TRUNCATE TABLE orders_archive RESTART IDENTITY;
TRUNCATE TABLE orders, order_items CASCADE;  -- 多表同時清空

-- 單欄主鍵
CREATE TABLE users (
    user_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL
);

-- 複合主鍵
CREATE TABLE order_items (
    order_id   bigint,
    product_id bigint,
    quantity   int NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE orders (
    order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT     -- 不允許刪除被引用的客戶
        ON UPDATE CASCADE      -- 客戶 ID 變更時自動更新
);

-- 延遲 FK 檢查（適合批量資料載入）
CREATE TABLE order_items (
    item_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id bigint,
    CONSTRAINT fk_item_order
        FOREIGN KEY (order_id) REFERENCES orders(order_id)
        DEFERRABLE INITIALLY DEFERRED  -- Transaction 結束時才檢查
);

-- 單欄唯一
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);

-- 部分唯一索引（只對符合條件的資料強制唯一）
CREATE UNIQUE INDEX uq_active_sku
ON products (sku)
WHERE is_active = true;
-- 非活躍產品可有重複 SKU，活躍產品不行

CREATE TABLE products (
    product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    price      numeric(10, 2) NOT NULL,
    sale_price numeric(10, 2),
    status     text NOT NULL,
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT chk_sale CHECK (sale_price IS NULL OR sale_price < price),
    CONSTRAINT chk_status CHECK (status IN ('draft', 'active', 'discontinued'))
);

-- 新增 CHECK 但跳過既有資料驗證（先上線，再排程驗證）
ALTER TABLE products
ADD CONSTRAINT chk_new CHECK (metadata IS NOT NULL) NOT VALID;

-- 後續驗證（只需 ShareUpdateExclusiveLock，不阻塞讀寫）
ALTER TABLE products VALIDATE CONSTRAINT chk_new;

-- 需要先安裝 btree_gist 擴展
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- 同一房間同一時段不可重複預約
CREATE TABLE room_bookings (
    booking_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id    int NOT NULL,
    during     tstzrange NOT NULL,
    CONSTRAINT no_overlap EXCLUDE USING gist (
        room_id WITH =,    -- 同一房間
        during WITH &&     -- 時間範圍重疊
    )
);

-- 建立分區父表
CREATE TABLE events (
    event_id   bigint NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    event_type text NOT NULL,
    payload    jsonb,
    PRIMARY KEY (event_id, created_at)  -- 主鍵必須包含分區鍵
) PARTITION BY RANGE (created_at);

-- 建立子分區（每月一個）
CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE events_2026_03 PARTITION OF events
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- 預設分區（捕獲未命中的資料）
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- 查詢時規劃器自動做 Partition Pruning
SELECT * FROM events
WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01';
-- 只掃描 events_2026_02

-- 動態建立下個月分區（適合放在定時任務中）
DO $$
DECLARE
    next_month date := date_trunc('month', now() + interval '1 month');
    partition_name text;
BEGIN
    partition_name := 'events_' || to_char(next_month, 'YYYY_MM');
    EXECUTE format(
        'CREATE TABLE IF NOT EXISTS %I PARTITION OF events
         FOR VALUES FROM (%L) TO (%L)',
        partition_name, next_month, next_month + interval '1 month'
    );
END $$;

CREATE TABLE orders (
    order_id bigint NOT NULL,
    region   text NOT NULL,
    amount   numeric(12, 2),
    PRIMARY KEY (order_id, region)
) PARTITION BY LIST (region);

CREATE TABLE orders_apac PARTITION OF orders
    FOR VALUES IN ('TW', 'JP', 'KR', 'SG');
CREATE TABLE orders_emea PARTITION OF orders
    FOR VALUES IN ('DE', 'FR', 'GB', 'NL');
CREATE TABLE orders_amer PARTITION OF orders
    FOR VALUES IN ('US', 'CA', 'MX', 'BR');
CREATE TABLE orders_other PARTITION OF orders DEFAULT;

CREATE TABLE user_activities (
    activity_id bigint NOT NULL,
    user_id     bigint NOT NULL,
    action      text,
    PRIMARY KEY (activity_id, user_id)
) PARTITION BY HASH (user_id);

CREATE TABLE user_activities_0 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_activities_1 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE user_activities_2 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE user_activities_3 PARTITION OF user_activities
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- 建立 Schema
CREATE SCHEMA IF NOT EXISTS reporting;
CREATE SCHEMA IF NOT EXISTS staging;

-- 在特定 Schema 中建立物件
CREATE TABLE reporting.monthly_summary (
    summary_id serial PRIMARY KEY,
    report_month date NOT NULL,
    total_sales numeric(15, 2)
);

-- 設定搜索路徑
SET search_path TO myapp, public;

-- 資料庫級設定（永久）
ALTER DATABASE mydb SET search_path TO myapp, public;

-- 授權
GRANT USAGE ON SCHEMA reporting TO readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO readonly_role;

-- 設定未來建立的表格也自動授權
ALTER DEFAULT PRIVILEGES IN SCHEMA reporting
    GRANT SELECT ON TABLES TO readonly_role;

-- 策略一：lock_timeout 避免長時間等待
BEGIN;
SET lock_timeout = '3s';
SET statement_timeout = '60s';
ALTER TABLE orders ADD COLUMN payment_method text;
COMMIT;
-- 超時則捕獲錯誤並稍後重試

-- 策略二：CREATE INDEX CONCURRENTLY（不阻塞讀寫）
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);
-- 注意：不能在 Transaction 中執行
-- 如果中途失敗，會留下 INVALID 索引，需手動 DROP 再重試

-- 策略三：NOT VALID + VALIDATE 兩步走
ALTER TABLE orders
ADD CONSTRAINT chk_amount CHECK (amount > 0) NOT VALID;
-- 第二步：驗證既有資料（不阻塞讀寫）
ALTER TABLE orders VALIDATE CONSTRAINT chk_amount;

CREATE SCHEMA IF NOT EXISTS myapp;
CREATE TABLE IF NOT EXISTS users (
    user_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL UNIQUE
);
CREATE INDEX IF NOT EXISTS idx_users_email ON users (email);

-- Enum 沒有 IF NOT EXISTS，需要用 DO 區塊
DO $$ BEGIN
    CREATE TYPE order_status AS ENUM ('pending', 'confirmed', 'shipped');
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

-- 新增欄位（PG 9.6+）
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_login timestamptz;

-- 確認規劃器是否正確修剪分區
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01';
-- 期望只掃描 events_2026_02

-- 查看分區列表
SELECT parent.relname AS parent,
       child.relname AS partition,
       pg_get_expr(child.relpartbound, child.oid) AS bound
FROM pg_class parent
JOIN pg_inherits ON inhparent = parent.oid
JOIN pg_class child ON inhrelid = child.oid
WHERE parent.relname = 'events'
ORDER BY child.relname;

-- 失效：對分區鍵做函數運算
SELECT * FROM events WHERE date_trunc('month', created_at) = '2026-02-01';

-- 正確：使用範圍條件
SELECT * FROM events
WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01';

-- 錯誤
CREATE TABLE events (
    event_id bigint PRIMARY KEY,  -- 不含分區鍵
    created_at timestamptz NOT NULL
) PARTITION BY RANGE (created_at);
-- ERROR: insufficient columns in PRIMARY KEY

-- 正確
CREATE TABLE events (
    event_id bigint NOT NULL,
    created_at timestamptz NOT NULL,
    PRIMARY KEY (event_id, created_at)
) PARTITION BY RANGE (created_at);

TRUNCATE TABLE customers;
-- ERROR: cannot truncate a table referenced in a FK constraint

TRUNCATE TABLE customers CASCADE;
-- 會同時清空 orders 等所有引用表——謹慎使用！

-- PG 11+：有預設值的 NOT NULL 欄位不需重寫表格（即時完成）
ALTER TABLE big_table ADD COLUMN processed boolean NOT NULL DEFAULT false;

-- PG 10 以前：需要重寫整張表，長時間持有排他鎖
-- 安全做法：先加可 NULL 欄位 → 批量填充 → 再設定 NOT NULL