PostgreSQL 学习笔记#

-- 创建数据库
CREATE DATABASE shop WITH ENCODING 'UTF8';

-- 创建表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,              -- 自增主键
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,     -- 原生布尔类型
    tags TEXT[],                         -- 数组类型
    metadata JSONB,                      -- JSON 文档
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 新版自增写法（推荐）
CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_no VARCHAR(20) NOT NULL
);

-- 字符串拼接
SELECT 'Hello' || ' ' || 'World';          -- → Hello World

-- 返回前 N 行（PG 用 LIMIT，也支持 FETCH）
SELECT * FROM users LIMIT 10;
SELECT * FROM users FETCH FIRST 10 ROWS ONLY;

-- 类型转换
SELECT '123'::INTEGER;                     -- → 123
SELECT created_at::DATE FROM users;        -- 时间戳转日期

-- 数组操作
SELECT tags FROM users WHERE 'admin' = ANY(tags);   -- 数组包含
SELECT array_length(tags, 1) FROM users;             -- 数组长度

-- DISTINCT ON（PG 特有：每组取第一条）
SELECT DISTINCT ON (department) * FROM employees ORDER BY department, salary DESC;
-- 每个部门薪资最高的员工

-- B-Tree（默认）
CREATE INDEX idx_name ON users(username);

-- GIN（JSONB 查询）
CREATE INDEX idx_metadata ON users USING GIN(metadata);

-- 查询 JSONB 字段
SELECT * FROM users WHERE metadata @> '{"role": "admin"}';

-- BRIN（大表时间列）
CREATE INDEX idx_created ON logs USING BRIN(created_at);

-- 部分索引（只索引活跃用户）
CREATE INDEX idx_active_users ON users(email) WHERE is_active = TRUE;

-- 表达式索引
CREATE INDEX idx_lower_name ON users(LOWER(username));
SELECT * FROM users WHERE LOWER(username) = 'alice';  -- 命中索引

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 或 ROLLBACK;

-- 手动 VACUUM（标记死元组空间可复用）
VACUUM users;

-- VACUUM FULL（锁表回收空间，慎用）
VACUUM FULL users;

-- ANALYZE（更新统计信息，优化器用）
ANALYZE users;

-- 自动清理配置
ALTER TABLE users SET (autovacuum = on, fillfactor = 90);

-- 插入 JSON
INSERT INTO users (metadata) VALUES ('{"name": "alice", "age": 30, "tags": ["admin", "vip"]}');

-- 查询 JSON 字段
SELECT metadata->>'name' AS name FROM users;              -- 文本提取
SELECT metadata->'age' FROM users;                         -- JSON 提取
SELECT * FROM users WHERE metadata @> '{"tags": ["admin"]}';  -- 包含
SELECT * FROM users WHERE metadata ? 'name';               -- 键存在
SELECT * FROM users WHERE metadata->>'age' > '25';         -- 条件查询

-- 更新 JSON
UPDATE users SET metadata = jsonb_set(metadata, '{age}', '31');
UPDATE users SET metadata = metadata || '{"city": "Shanghai"}';  -- 合并

-- 创建 GIN 索引
CREATE INDEX idx_meta ON users USING GIN(metadata);

-- 高效查询 JSON 任意字段
SELECT * FROM users WHERE metadata @> '{"role": "admin"}';
-- 等价于 MongoDB: db.users.find({"role": "admin"})

-- 聚合 JSON
SELECT metadata->>'city' AS city, COUNT(*) 
FROM users 
GROUP BY metadata->>'city';

-- 每个部门薪资排名
SELECT 
    name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    SUM(salary) OVER () AS total_salary
FROM employees;

-- 累计求和
SELECT 
    date, revenue,
    SUM(revenue) OVER (ORDER BY date) AS cumulative
FROM daily_sales;

-- 基本 CTE
WITH high_value_customers AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
    HAVING SUM(amount) > 10000
)
SELECT c.name, h.total
FROM customers c
JOIN high_value_customers h ON c.id = h.customer_id;

-- 递归 CTE：组织架构树
WITH RECURSIVE org_tree AS (
    SELECT id, name, parent_id, 0 AS level
    FROM departments WHERE parent_id IS NULL
    UNION ALL
    SELECT d.id, d.name, d.parent_id, t.level + 1
    FROM departments d
    JOIN org_tree t ON d.parent_id = t.id
)
SELECT id, name, level FROM org_tree ORDER BY level;

CREATE OR REPLACE FUNCTION transfer_funds(
    p_from INT, p_to INT, p_amount DECIMAL(10,2)
) RETURNS BOOLEAN AS $$
BEGIN
    UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Source account not found';
    END IF;
    
    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Target account not found';
    END IF;
    
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT transfer_funds(1, 2, 100.00);

-- 审计触发器
CREATE OR REPLACE FUNCTION audit_log() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_table (table_name, operation, old_data, new_data, changed_at)
    VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), row_to_json(NEW), NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_audit
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW EXECUTE FUNCTION audit_log();

-- 按范围分区（如按日期）
CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (order_date);

-- 创建分区
CREATE TABLE orders_2024_01 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- 查询自动路由到对应分区
SELECT * FROM orders WHERE order_date = '2024-01-15';
-- 只扫描 orders_2024_01 分区

EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'alice';
-- EXPLAIN：显示执行计划
-- ANALYZE：实际执行并显示耗时