-- 范式化：需要 JOIN
SELECT e.name, d.budget FROM employees e JOIN departments d ON e.department = d.name;

-- 反范式化：冗余字段，减少 JOIN
CREATE TABLE employees_denorm (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT,
    department TEXT,
    dept_budget NUMERIC(15,2)  -- 冗余字段
);

-- 父表
CREATE TABLE vehicles (
    id SERIAL PRIMARY KEY,
    make TEXT NOT NULL,
    model TEXT NOT NULL,
    year INT NOT NULL
);

-- 子表继承父表
CREATE TABLE cars () INHERITS (vehicles);
CREATE TABLE trucks () INHERITS (vehicles);

ALTER TABLE cars ADD COLUMN num_doors INT DEFAULT 4;
ALTER TABLE trucks ADD COLUMN payload_kg NUMERIC(10,2);

-- 查询父表会包含子表数据
SELECT * FROM vehicles;         -- 所有车辆（包括 cars 和 trucks）
SELECT ONLY * FROM vehicles;    -- 仅父表数据

-- 约束继承
CREATE TABLE cars (
    num_doors INT CHECK (num_doors BETWEEN 2 AND 5)
) INHERITS (vehicles);

-- 创建分区主表
CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total NUMERIC(12,2) NOT NULL,
    status SMALLINT DEFAULT 0
) PARTITION BY RANGE (order_date);

-- 创建分区
CREATE TABLE orders_2023 PARTITION OF orders
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE orders_2025 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- 默认分区（处理不在任何范围的数据）
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- 在分区上创建索引（自动在所有分区创建）
CREATE INDEX idx_orders_date ON orders (order_date);
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- 插入数据会自动路由到对应分区
INSERT INTO orders (customer_id, order_date, total) VALUES
    (1, '2024-06-15', 100.00),
    (2, '2023-12-25', 250.00);

-- 查询特定分区
SELECT * FROM orders_2024;
-- 查询整个表（自动扫描所有分区）
SELECT * FROM orders WHERE order_date >= '2024-01-01';

CREATE TABLE sales (
    id BIGSERIAL,
    region TEXT NOT NULL,
    amount NUMERIC(12,2) NOT NULL,
    sale_date DATE NOT NULL
) PARTITION BY LIST (region);

CREATE TABLE sales_north PARTITION OF sales FOR VALUES IN ('North', 'Northeast');
CREATE TABLE sales_south PARTITION OF sales FOR VALUES IN ('South', 'Southeast');
CREATE TABLE sales_east PARTITION OF sales FOR VALUES IN ('East');
CREATE TABLE sales_west PARTITION OF sales FOR VALUES IN ('West');
CREATE TABLE sales_default PARTITION OF sales DEFAULT;

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    name TEXT NOT NULL,
    email TEXT NOT NULL
) PARTITION BY HASH (id);

CREATE TABLE users_p0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE users_p1 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE users_p2 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE users_p3 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 3);

CREATE TABLE events (
    id BIGSERIAL,
    event_date DATE NOT NULL,
    region TEXT NOT NULL,
    data JSONB
) PARTITION BY RANGE (event_date);

CREATE TABLE events_2024 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
    PARTITION BY LIST (region);

CREATE TABLE events_2024_north PARTITION OF events_2024 FOR VALUES IN ('North');
CREATE TABLE events_2024_south PARTITION OF events_2024 FOR VALUES IN ('South');
CREATE TABLE events_2024_default PARTITION OF events_2024 DEFAULT;

-- 分离分区（不删除数据）
ALTER TABLE orders DETACH PARTITION orders_2023;

-- 分离分区并发（PG 14+，不阻塞查询）
ALTER TABLE orders DETACH PARTITION orders_2023 CONCURRENTLY;

-- 附加分区
ALTER TABLE orders ATTACH PARTITION orders_2023
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- 自动创建分区（使用 pg_partman 扩展）
-- CREATE EXTENSION pg_partman;
-- SELECT partman.create_parent('public.orders', 'order_date', 'native', 'monthly');

-- 查看分区信息
SELECT
    parent.relname AS parent,
    child.relname AS partition,
    pg_get_expr(child.relpartbound, child.oid) AS bounds
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
WHERE parent.relname = 'orders'
ORDER BY child.relname;

-- 创建表空间（需要 OS 目录权限）
CREATE TABLESPACE fast_ssd LOCATION '/mnt/ssd/pgdata';

-- 在指定表空间创建表
CREATE TABLE hot_data (
    id SERIAL PRIMARY KEY,
    data TEXT
) TABLESPACE fast_ssd;

-- 在指定表空间创建索引
CREATE INDEX idx_hot_data ON hot_data (id) TABLESPACE fast_ssd;

-- 修改默认表空间
SET default_tablespace = fast_ssd;

-- 移动表到新表空间
ALTER TABLE hot_data SET TABLESPACE pg_default;

-- 查看表空间
SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname))
FROM pg_tablespace;

-- 会话级临时表（会话结束自动删除）
CREATE TEMP TABLE tmp_results AS
SELECT department, AVG(salary) AS avg_sal
FROM employees GROUP BY department;

-- 事务级临时表
CREATE TEMP TABLE tmp_txn_data (
    id INT, value TEXT
) ON COMMIT DROP;

-- 临时表不影响其他会话
-- 临时表不会被 pg_dump 导出

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price NUMERIC(10,2) NOT NULL,
    tax_rate NUMERIC(4,2) NOT NULL DEFAULT 0.13,
    price_with_tax NUMERIC(10,2) GENERATED ALWAYS AS (price * (1 + tax_rate)) STORED
);

INSERT INTO products (price, tax_rate) VALUES (100.00, 0.13);
SELECT * FROM products;
-- id | price  | tax_rate | price_with_tax
-- 1  | 100.00 | 0.13     | 113.00

COMMENT ON TABLE orders IS '订单主表';
COMMENT ON COLUMN orders.status IS '订单状态：0=待支付 1=已支付 2=已发货 3=已完成 9=已取消';