-- 一条查询里能同时用到本页大半章节
WITH recent AS (                                  -- 07 章 CTE
  SELECT user_id, amount, created_at
  FROM orders
  WHERE created_at >= now() - '30 days'::interval  -- 02 章
)
SELECT u.name,
       count(*)                        AS orders,     -- 05 章聚合
       sum(r.amount)                   AS total,
       rank() OVER (ORDER BY sum(r.amount) DESC) AS rk  -- 06 章窗口
FROM users u
JOIN recent r ON r.user_id = u.id                     -- 04 章 JOIN
GROUP BY u.id, u.name
HAVING count(*) > 1
ORDER BY total DESC, u.id                             -- 08 章：带 tiebreaker
LIMIT 20;

-- 一段很「PG」的查询：CTE + JSONB + 窗口函数同框
-- （先睹为快，现在看不懂没关系：CTE 见 07 章、窗口函数见 06 章、JSONB 见 11 章）
WITH ranked AS (
  SELECT name,
         profile->>'city' AS city,      -- JSONB 取字段
         rank() OVER (
           PARTITION BY profile->>'city'
           ORDER BY score DESC
         ) AS rnk
  FROM players
)
SELECT * FROM ranked WHERE rnk <= 3;  -- 各城市前三名

-- 从零到第一条查询：五条命令
$ docker run -d --name pg -e POSTGRES_PASSWORD=pw -p 5432:5432 postgres:18
$ docker exec -it pg psql -U postgres

postgres=# CREATE DATABASE shop;
postgres=# \c shop
shop=# CREATE TABLE users (
  id         bigserial PRIMARY KEY,
  name       text NOT NULL,
  created_at timestamptz DEFAULT now()
);
shop=# INSERT INTO users (name) VALUES ('alice'), ('bob') RETURNING id;
 id
----
  1
  2
shop=# \d users              -- 看结构，比 SELECT 更常用
shop=# SELECT * FROM users ORDER BY id;

-- 应用里按错误码判断，而不是按消息文本（消息会随版本与语言变）
DO $$
BEGIN
  INSERT INTO users (id, name) VALUES (1, 'dup');
EXCEPTION
  WHEN unique_violation THEN          -- 等价于 SQLSTATE '23505'
    RAISE NOTICE '已存在，跳过';
END $$;

-- psql 里把错误码也打出来
shop=# \set VERBOSITY verbose
shop=# SELECT * FROM nope;
ERROR:  42P01: relation "nope" does not exist
LINE 1: SELECT * FROM nope;
                      ^
LOCATION:  parserOpenTable, parse_relation.c:1449

-- 七个最常撞的，照着认即可

-- 42P01  relation "userz" does not exist
--        表名拼错，或建表时加了双引号带大写

-- 42703  column "nosuch" does not exist
--        列名拼错；也可能是把字符串写成了双引号，"Alice" 被当成列名

-- 42601  syntax error at or near "email"
--        语法错。看 at or near 指的词，问题通常在它前面（比如漏了逗号）

-- 23505  duplicate key value violates unique constraint "users_email_key"
--        唯一约束冲突。约束名直接告诉你是 users 表的 email 列

-- 23503  insert or update on table "orders" violates foreign key constraint
--        外键指向的行不存在，或删父行时还有子行引用着

-- 23502  null value in column "name" of relation "users" violates not-null constraint
--        插入时漏了必填列

-- 22P02  invalid input syntax for type integer: "abc"
--        类型不匹配，把字符串塞给了 int 列

-- 让 psql 显示错误码本身（默认不显示）
\set VERBOSITY verbose

SELECT id, name, email, created_at
FROM users
WHERE created_at >= '2025-01-01'
  AND status = 'active'
  AND (role = 'admin' OR role = 'editor')
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;  -- 分页：第1页

-- 常用过滤操作符
WHERE age BETWEEN 18 AND 65
WHERE status IN ('active', 'pending')
WHERE email LIKE '%@gmail.com'     -- 区分大小写
WHERE name ILIKE '%alice%'       -- 不区分大小写（PG特有）
WHERE deleted_at IS NULL          -- 不能用 = NULL

-- DISTINCT / DISTINCT ON
SELECT DISTINCT country FROM users;
SELECT DISTINCT ON (user_id) * FROM orders
ORDER BY user_id, created_at DESC;
-- ↑ 每个用户只取最新一条订单（PG 特有便利写法）

-- CASE 表达式
SELECT name,
  CASE
    WHEN age < 18 THEN '未成年'
    WHEN age < 65 THEN '成年'
    ELSE '老年'
  END AS age_group
FROM users;

CREATE TABLE products (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  sku           varchar(50) UNIQUE NOT NULL,
  name          text NOT NULL,
  price         numeric(10,2) NOT NULL CHECK (price >= 0),
  stock         integer DEFAULT 0,
  category_id   int REFERENCES categories(id) ON DELETE SET NULL,
  tags          text[],                    -- 数组类型
  metadata      jsonb DEFAULT '{}',         -- JSON类型
  is_active     boolean DEFAULT TRUE,
  created_at    timestamptz DEFAULT now(), -- 带时区时间戳（推荐）
  CONSTRAINT valid_sku CHECK (sku ~ '^[A-Z0-9-]+$')
);

-- 常用类型速查
-- text / varchar(n)  字符串（PG 中性能无差）
-- integer / bigint   整数
-- numeric(p,s)       精确小数（货币必用）
-- boolean            true/false
-- timestamptz        带时区时间（强烈推荐）
-- uuid               全局唯一标识符
-- jsonb              二进制JSON（支持索引）
-- text[]             数组

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled');

-- INSERT：单行 / 多行插入
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com'),
       ('Bob',   'bob@example.com')
RETURNING id, created_at;  -- 直接拿回生成的主键等字段

-- UPDATE：修改已有行（WHERE 决定改哪些行）
UPDATE products
SET price = price * 0.9, updated_at = now()
WHERE category_id = 3
RETURNING id, price;

-- DELETE：删除行
DELETE FROM sessions
WHERE expires_at < now()
RETURNING id;

-- ALTER TABLE：修改表结构
ALTER TABLE users ADD COLUMN phone text;
ALTER TABLE users RENAME COLUMN phone TO mobile;
ALTER TABLE users ALTER COLUMN mobile SET NOT NULL;
ALTER TABLE users DROP COLUMN mobile;
-- PG 的 DDL 是事务性的：以上语句都可以放进事务回滚（见 10 章）

-- 建三行：两行 dept='x'，一行 dept 为 NULL
-- id | name | dept
--  1 |  a   |  x
--  2 |  b   |  x
--  3 |  c   | NULL

SELECT count(*) FROM emp;                         -- 3
SELECT count(*) FROM emp WHERE dept = 'x';         -- 2
SELECT count(*) FROM emp WHERE dept <> 'x';        -- 0 ← 那第三行呢？

-- 第三行的判断是 NULL <> 'x' → NULL，不是 true，所以被 WHERE 丢掉了
-- 两种把它捞回来的写法：
SELECT count(*) FROM emp WHERE dept <> 'x' OR dept IS NULL;   -- 1
SELECT count(*) FROM emp WHERE dept IS DISTINCT FROM 'x';       -- 1

-- IS DISTINCT FROM：把 NULL 当普通值比，结果永不为 NULL
SELECT null IS DISTINCT FROM null;   -- false（两边都是 NULL，"不相异"）
SELECT 1 IS DISTINCT FROM null;      -- true

-- 直接写常量就能复现
SELECT count(*) FROM emp WHERE id NOT IN (1, 2);      -- 1  正常
SELECT count(*) FROM emp WHERE id NOT IN (1, null);   -- 0  一行都没有！
SELECT count(*) FROM emp WHERE id IN (1, null);       -- 1  IN 反而正常

-- 真实场景：子查询的列可空，你根本看不出来
-- 「找出所有不是别人上级的员工」——emp.mgr 允许为 NULL
SELECT count(*) FROM emp
WHERE id NOT IN (SELECT mgr FROM emp);        -- 0  ← 错，因为 mgr 里有 NULL

-- 改成 NOT EXISTS，对 NULL 免疫
SELECT count(*) FROM emp e
WHERE NOT EXISTS (SELECT 1 FROM emp m WHERE m.mgr = e.id);   -- 2  ← 对

-- 非要用 NOT IN，就得自己把 NULL 挡掉
SELECT count(*) FROM emp
WHERE id NOT IN (SELECT mgr FROM emp WHERE mgr IS NOT NULL);

-- emp 有 3 行，其中 1 行 salary 为 NULL（100、200、NULL）
SELECT count(*) AS 行数, count(salary) AS 有工资的 FROM emp;
-- 3 | 2

SELECT sum(salary), count(salary), avg(salary) FROM emp;
-- 300 | 2 | 150   ← 平均值的分母是 2 不是 3

-- 空结果集：只有 count 给 0，其余给 NULL
SELECT sum(salary) FROM emp WHERE false;              -- NULL
SELECT count(*)    FROM emp WHERE false;              -- 0
SELECT coalesce(sum(salary), 0) FROM emp WHERE false; -- 0  ← 该这么写

-- 排序：NULL 被当作最大值
SELECT x FROM (VALUES(2),(null),(1)) v(x) ORDER BY x;
-- 1, 2, NULL      升序时 NULL 在最后
SELECT x FROM (VALUES(2),(null),(1)) v(x) ORDER BY x DESC;
-- NULL, 2, 1      倒序时 NULL 抢到最前面！
SELECT x FROM (VALUES(2),(null),(1)) v(x) ORDER BY x DESC NULLS LAST;
-- 2, 1, NULL      这才是「排行榜」想要的

-- NULLIF：反过来把某个值变成 NULL，最常见的用法是防除零
SELECT total / nullif(cnt, 0) FROM stats;  -- cnt 为 0 时得 NULL，不报错

-- INNER JOIN：只返回两表都匹配的行
SELECT o.id, u.name, o.total
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

-- LEFT JOIN：保留左表全部行
SELECT u.name, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- 查找"没有订单的用户"（LEFT JOIN + IS NULL 反连接）
SELECT u.* FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- FULL OUTER JOIN：两边都保留
SELECT * FROM table_a
FULL OUTER JOIN table_b ON table_a.id = table_b.a_id;

-- CROSS JOIN：笛卡尔积
SELECT s.name, c.name FROM sizes s CROSS JOIN colors c;

-- 多表 JOIN
SELECT o.id, u.name AS customer, p.name AS product, oi.quantity
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id;

-- 自连接：员工和经理在同一张表
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- UNION ALL：合并但不去重（性能更好）
SELECT id FROM active_users
UNION ALL
SELECT id FROM archived_users;

-- INTERSECT：交集
SELECT user_id FROM bought_product_a
INTERSECT
SELECT user_id FROM bought_product_b;

-- EXCEPT：差集
SELECT user_id FROM all_users
EXCEPT
SELECT user_id FROM users_who_purchased;

-- ✓ 条件在 ON：保留「空部门 0」这一行（3 行）
SELECT d.name, count(e.id)
FROM dept d
LEFT JOIN emp e ON e.dept_id = d.id AND e.salary > 150
GROUP BY d.name;

-- ✗ 条件在 WHERE：空部门整行消失（2 行）
SELECT d.name, count(e.id)
FROM dept d
LEFT JOIN emp e ON e.dept_id = d.id
WHERE e.salary > 150            -- NULL > 150 是 NULL
GROUP BY d.name;

-- 例外：这个 WHERE 是故意的（反连接，见本章后面）
WHERE e.id IS NULL;

-- ✗ 子查询里只要有一个 NULL，结果就是空（0 行）
SELECT * FROM emp
WHERE dept_id NOT IN (SELECT dept_id FROM emp);

-- ✓ 反连接：对 NULL 免疫（1 行）
SELECT * FROM emp e
WHERE NOT EXISTS (
  SELECT 1 FROM emp x WHERE x.dept_id = e.dept_id
);

-- ✓ 反连接的另一种写法（同样 1 行）
SELECT d.* FROM dept d
LEFT JOIN emp e ON e.dept_id = d.id
WHERE e.id IS NULL;

-- 半连接：只判断存在，不取右表字段
SELECT * FROM dept d
WHERE EXISTS (SELECT 1 FROM emp e WHERE e.dept_id = d.id);

SELECT
  COUNT(*) AS total_orders,
  COUNT(DISTINCT user_id) AS unique_users,
  SUM(total) AS revenue,
  ROUND(AVG(total)::numeric, 2) AS avg_order_value,
  MIN(total), MAX(total)
FROM orders;

-- 按月分组
SELECT
  date_trunc('month', created_at) AS month,
  COUNT(*) AS order_count,
  SUM(total) AS monthly_revenue
FROM orders
GROUP BY 1
ORDER BY month;

-- HAVING：对聚合结果再过滤
SELECT user_id, COUNT(*) AS cnt, SUM(total) AS total_spent
FROM orders
WHERE status = 'completed'     -- 先按行过滤
GROUP BY user_id
HAVING SUM(total) > 1000     -- 再按聚合结果过滤
ORDER BY total_spent DESC;

-- ROLLUP：多层级汇总
SELECT category, region, SUM(sales)
FROM sales_data
GROUP BY ROLLUP (category, region);
-- 生成 (category,region)、(category)、(总计) 三级汇总

-- 4 行，其中 dept_id 有一个 NULL
SELECT count(*),              -- 4
       count(dept_id),        -- 3  跳过 NULL
       count(DISTINCT dept_id), -- 2
       sum(dept_id),          -- 40
       avg(dept_id)           -- 13.33（除以 3）
FROM emp;

-- 空集：count 给 0，sum 给 NULL
SELECT count(*),            -- 0
       sum(salary),         -- NULL，不是 0
       coalesce(sum(salary), 0)  -- 想要 0 就这么写
FROM emp WHERE 1=0;

-- GROUP BY：NULL 自成一组，不会消失
SELECT dept_id, count(*) FROM emp
GROUP BY dept_id ORDER BY dept_id NULLS LAST;

-- ROLLUP：多出一行总计（东30 / 西70 / NULL100）
SELECT region, sum(amt)
FROM sale GROUP BY ROLLUP(region);

-- 用 GROUPING 区分「汇总行的 NULL」与「数据里的 NULL」
SELECT CASE WHEN grouping(region) = 1
            THEN '合计' ELSE region END AS region,
       sum(amt)
FROM sale
GROUP BY ROLLUP(region)
ORDER BY grouping(region), region;   -- 总计稳定在末尾

-- 精确指定要哪几种组合；() 就是总计行
SELECT region, product, sum(amt)
FROM sale
GROUP BY GROUPING SETS ((region), (product), ());

-- 每个用户的订单 + 该用户的汇总
SELECT
  user_id, id AS order_id, total,
  COUNT(*) OVER (PARTITION BY user_id) AS user_order_count,
  SUM(total) OVER (PARTITION BY user_id) AS user_total_spent
FROM orders;

-- 排名函数
SELECT name, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,   -- 1,2,3,4
  RANK()       OVER (ORDER BY score DESC) AS rank,      -- 1,2,2,4
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank -- 1,2,2,3
FROM leaderboard;

-- 每组前 N（每个分类销量前3）
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY category ORDER BY sales DESC
  ) AS rn
  FROM products
)
SELECT * FROM ranked WHERE rn <= 3;

-- 月度环比增长
SELECT
  month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month))
    / LAG(revenue) OVER (ORDER BY month) * 100, 2
  ) AS growth_pct
FROM monthly_revenue;

-- FIRST_VALUE：分组内第一个值
SELECT user_id, order_date, total,
  FIRST_VALUE(total) OVER (
    PARTITION BY user_id ORDER BY order_date
  ) AS first_order_total
FROM orders;

-- 7天移动平均
SELECT date, revenue,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_revenue;

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

-- 数据：1, 1, 2, 3

-- 默认帧是 RANGE：2, 2, 4, 7
SELECT v, sum(v) OVER (ORDER BY v) FROM s;

-- 显式 ROWS 才是逐行累计：1, 2, 4, 7
SELECT v, sum(v) OVER (
  ORDER BY v
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) FROM s;

-- 无 ORDER BY：帧是整个分区，每行都是总和 7
SELECT v, sum(v) OVER () FROM s;

-- 7 日移动平均：必须用 ROWS
avg(amt) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

-- 标量子查询
SELECT name, price,
  price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;

-- IN 子查询
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

-- EXISTS（与 IN 常等价；NOT EXISTS 可避开 NOT IN 的 NULL 陷阱）
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 1000
);

-- 派生表（子查询作 FROM，必须起别名）
SELECT category, avg_price FROM (
  SELECT category, AVG(price) AS avg_price
  FROM products GROUP BY category
) AS category_avg
WHERE avg_price > 100;

-- CTE：命名子查询，可读性大幅提升
WITH monthly_sales AS (
  SELECT date_trunc('month', created_at) AS month,
         SUM(total) AS revenue
  FROM orders GROUP BY 1
),
top_months AS (
  SELECT * FROM monthly_sales ORDER BY revenue DESC LIMIT 3
)
SELECT * FROM top_months;

-- 递归 CTE：遍历组织架构树
WITH RECURSIVE org_chart AS (
  -- 基准：根节点
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  -- 递归：找下属
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT REPEAT('  ', level - 1) || name AS org_tree
FROM org_chart ORDER BY level;

-- 强制物化（CTE 被多次引用时避免重复计算）
WITH expensive_calc AS MATERIALIZED (
  SELECT * FROM big_table WHERE complex_condition
)
SELECT * FROM expensive_calc a JOIN expensive_calc b ON ...;

-- ❌ created 有重复值时，翻页会重复/漏行
SELECT * FROM orders
ORDER BY created DESC
LIMIT 20 OFFSET 20;

-- ✅ 追加主键作为 tiebreaker，排序全序化
SELECT * FROM orders
ORDER BY created DESC, id DESC
LIMIT 20 OFFSET 20;

-- 排序列可空时，还要显式指定 NULL 的位置
-- PG 把 NULL 当作最大值：DESC 时它会抢到最前面（见 03 章）
SELECT * FROM orders
ORDER BY amount DESC NULLS LAST, id DESC
LIMIT 10;

-- 想让索引完全服务于这个排序，索引的列序与方向要对上：
CREATE INDEX idx_orders_created_id ON orders (created DESC, id DESC);

-- ❌ 深分页：越往后越慢（20 万行的表上，OFFSET 0 约 2ms，
--    OFFSET 199980 约 23ms；表越大、翻得越深，差距越夸张）
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 199980;

-- ✅ keyset 分页：第一页照常
SELECT * FROM orders ORDER BY id DESC LIMIT 20;

-- 后续页：带上上一页最后一行的 id，直接定位（约 1ms，且不随页数增长）
SELECT * FROM orders
WHERE id < 199980          -- 上一页最后一行的 id
ORDER BY id DESC
LIMIT 20;

-- 多列排序时用行值比较，语义正好是「字典序的下一个」
SELECT * FROM orders
WHERE (created, id) < ('2026-07-01 10:00:00+08', 12345)
ORDER BY created DESC, id DESC
LIMIT 20;
-- 注意：(a,b) < (x,y) 是行值比较，不等于 a<x AND b<y，后者是错的

-- 配套索引（列序与方向要和 ORDER BY 完全一致）
CREATE INDEX ON orders (created DESC, id DESC);

-- B-tree 索引（默认）
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created ON orders(created_at DESC);

-- 复合索引：列顺序决定可用性
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- ✅ WHERE user_id = ?              用得上（命中最左列）
-- ✅ WHERE user_id = ? AND status = ?  最理想
-- ⚠️ WHERE status = ?              跳过最左列：能不能用要看选择性，
--    命中行少时 PG 可能整段扫索引再过滤，多了就直接全表——别指望

-- 唯一索引
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- 部分索引：只索引未删除的行
CREATE INDEX idx_active_users ON users(email)
  WHERE deleted_at IS NULL;

-- 表达式索引
CREATE INDEX idx_lower_email ON users(LOWER(email));

-- GIN 索引：数组、JSONB、全文搜索
CREATE INDEX idx_tags ON products USING GIN(tags);
CREATE INDEX idx_meta ON products USING GIN(metadata);

-- 查看执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

-- 实际执行并显示真实耗时（注意：会真正执行！）
EXPLAIN (ANALYZE, BUFFERS)
  SELECT * FROM orders WHERE user_id = 123;

-- 常见扫描方式：
-- Seq Scan        全表扫描（大表出现 → 可能缺索引）
-- Index Scan      走索引 + 回表
-- Index Only Scan 索引覆盖，无需回表（最快）
-- Bitmap Heap Scan 索引位图 + 批量读取
-- Hash Join       大表 JOIN 常见

-- 更新统计信息 + 清理死元组（死元组 = MVCC 留下的旧行版本，见 10 章）
VACUUM ANALYZE orders;
-- 日常由 autovacuum 自动做，不用手动跑；下面两种情况才需要你出手：
--   ① 刚批量导入/大改一批数据，想立刻让规划器拿到新统计 → ANALYZE
--   ② 表明显膨胀（见下方 pitfall 的查法）→ 先查清 autovacuum 为何没跟上

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
  UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT; -- 两条都成功才生效

-- 出错时回滚
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
ROLLBACK; -- 撤销，余额恢复

-- SAVEPOINT：事务内部分回滚
BEGIN;
  INSERT INTO orders ...;
  SAVEPOINT before_payment;
    UPDATE inventory SET stock = stock - 1;
    -- 支付失败！
  ROLLBACK TO before_payment; -- 只撤销库存，订单保留
COMMIT;

-- 设隔离级别：必须在事务内，最省事的是直接写进 BEGIN
BEGIN ISOLATION LEVEL REPEATABLE READ;
  -- ... 本事务内所有语句看同一份快照 ...
COMMIT;

-- 等价写法：先 BEGIN 再 SET（SET 必须紧跟 BEGIN，且在任何查询之前）
BEGIN;
  SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
COMMIT;

-- 改本连接的默认（之后每个事务都用它），PG 默认是 READ COMMITTED
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 行级锁：FOR UPDATE
BEGIN;
  SELECT * FROM inventory WHERE product_id = 1 FOR UPDATE;
  -- 其他事务的 UPDATE/DELETE/FOR UPDATE 会被阻塞
  UPDATE inventory SET stock = stock - 1 WHERE product_id = 1;
COMMIT;

-- 乐观锁（避免长时间持有锁）
UPDATE inventory
SET stock = stock - 1, version = version + 1
WHERE product_id = 1 AND version = 5;
-- 影响行数为0 → 数据已被修改，需重试

-- 取值：-> 返回 jsonb，->> 返回 text
SELECT
  settings -> 'theme' AS theme_json,   -- "dark"（jsonb）
  settings ->> 'theme' AS theme_text, -- dark（text）
  settings -> 'notifications' ->> 'email' AS email_pref
FROM users;

-- 路径访问
SELECT settings #>> '{notifications,email}' FROM users;

-- 查询：@> 包含操作符（可走 GIN 索引）
SELECT * FROM users WHERE settings @> '{"theme": "dark"}';

-- 更新
UPDATE users SET settings = jsonb_set(settings, '{theme}', '"light"');
-- 合并
UPDATE users SET settings = settings || '{"lang": "zh-CN"}'::jsonb;
-- 删除
UPDATE users SET settings = settings - 'theme';

-- 展开 JSON 数组为行
SELECT id, elem.value AS tag
FROM products, jsonb_array_elements_text(metadata -> 'tags') AS elem(value);
-- 注意展开的是 jsonb 列里的数组；products.tags 是 text[]，那个要用 unnest(tags)

-- 聚合成 JSON
SELECT jsonb_agg(jsonb_build_object('id', id, 'name', name))
FROM products;

-- 基本操作
SELECT ARRAY['a', 'b', 'c'];
SELECT tags[1];                  -- 第一个元素（从1开始！）
SELECT array_length(tags, 1);

-- 包含查询
SELECT * FROM products WHERE 'sale' = ANY(tags);
SELECT * FROM products WHERE tags @> ARRAY['sale']; -- 可走GIN
SELECT * FROM products WHERE tags && ARRAY['sale','new']; -- 有交集

-- 展开 / 聚合
SELECT id, unnest(tags) AS tag FROM products;
SELECT category, array_agg(name) FROM products GROUP BY category;

-- 追加 / 移除
UPDATE products SET tags = array_append(tags, 'new');
UPDATE products SET tags = array_remove(tags, 'old');

-- STRING_AGG：多行拼成字符串
SELECT category, string_agg(name, ', ' ORDER BY name)
FROM products GROUP BY category;

-- 同一段文本，两种类型存完不一样
SELECT '{"b":1,"a":2,"a":3}'::json::text;
--  {"b":1,"a":2,"a":3}      原样保留
SELECT '{"b":1,"a":2,"a":3}'::jsonb::text;
--  {"a": 3, "b": 1}         重排 + 去重 + 规范空白

-- jsonb 按语义比较：true；json 只能比文本：false
SELECT '{"a":1,"b":2}'::jsonb = '{"b":2,"a":1}'::jsonb;

-- -> 给 jsonb（带引号），->> 给 text（裸值）
SELECT '{"a":"x"}'::jsonb ->  'a';   -- "x"
SELECT '{"a":"x"}'::jsonb ->> 'a';   -- x

-- 深层路径：比一串 -> 好读
SELECT doc #>> '{user,addr,city}' FROM t;

-- 普通视图
CREATE VIEW active_user_summary AS
SELECT u.id, u.name,
  COUNT(o.id) AS order_count,
  COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.deleted_at IS NULL
GROUP BY u.id, u.name;

SELECT * FROM active_user_summary WHERE total_spent > 1000;

-- 用途：隐藏敏感列 / 简化复杂查询 / 向后兼容旧接口

-- 物化视图
CREATE MATERIALIZED VIEW daily_sales AS
SELECT date_trunc('day', created_at) AS day,
       SUM(total) AS revenue
FROM orders GROUP BY 1;

REFRESH MATERIALIZED VIEW daily_sales;
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;
-- CONCURRENTLY 不锁表，但需要唯一索引

-- SQL 函数（可被优化器内联）
CREATE FUNCTION full_name(first text, last text)
RETURNS text LANGUAGE sql IMMUTABLE AS $$
  SELECT first || ' ' || last;
$$;

-- PL/pgSQL 函数（流程控制 + 异常处理）
CREATE FUNCTION transfer_funds(
  from_acc uuid, to_acc uuid, amount numeric
) RETURNS void AS $$
DECLARE
  bal numeric;
BEGIN
  SELECT balance INTO bal
  FROM accounts WHERE id = from_acc FOR UPDATE;

  IF bal < amount THEN
    RAISE EXCEPTION '余额不足: % < %', bal, amount;
  END IF;

  UPDATE accounts SET balance = balance - amount WHERE id = from_acc;
  UPDATE accounts SET balance = balance + amount WHERE id = to_acc;
END;
$$ LANGUAGE plpgsql;

-- ❌ 绝对不要这样（无论哪种语言、哪个框架）
--    "SELECT * FROM users WHERE id = " + userInput

-- ✅ SQL 里写 $1 $2，值单独传
SELECT * FROM users WHERE id = $1 AND status = $2;

-- Node.js（node-postgres）
--   await pool.query('SELECT * FROM users WHERE id = $1', [userId]);

-- Python（psycopg 3）——注意占位符是 %s，不是 $1，且第二参必须是元组
--   cur.execute("SELECT * FROM users WHERE id = %s", (user_id,))

-- 表名/列名要动态：先白名单，别直接拼
--   const ALLOWED = { created: 'created', amount: 'amount' };
--   const col = ALLOWED[req.query.sort] ?? 'created';   // 查不到就用默认

-- 实在要在 SQL 内部拼标识符，用 format 的 %I（自动加引号并转义）
SELECT format('SELECT * FROM %I WHERE %I = $1', 'users', 'id');
-- %I = 标识符，%L = 字面量，%s = 原样插入（%s 不安全，别用它拼用户输入）

-- 看当前连接情况
SHOW max_connections;                              -- 默认 100
SELECT count(*) FROM pg_stat_activity;              -- 当前连接数

-- 按状态分组，排查连接是被谁占着的
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
-- idle                    空闲，正常（池子里待命的连接）
-- active                  正在跑查询
-- idle in transaction     ⚠️ 开了事务却不提交——这个数字持续大于 0 就要查

-- 找出长时间挂着的事务（它们会阻止 VACUUM 回收空间）
SELECT pid, state, now() - xact_start AS 事务时长, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

-- Node.js：用 Pool 而不是 Client，全进程共用一个
--   const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 20 });
--   const { rows } = await pool.query('SELECT 1');   // 自动借还，不用手动管

-- 代码里事务的标准骨架（Node.js / node-postgres）：
--   const client = await pool.connect();        // 从池子借一个连接
--   try {
--     await client.query('BEGIN');
--     await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amt, from]);
--     await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amt, to]);
--     await client.query('COMMIT');
--   } catch (e) {
--     await client.query('ROLLBACK');           // 任何异常都要回滚
--     throw e;
--   } finally {
--     client.release();                        // 无论如何都要还回去
--   }

-- 按错误码分流，别去匹配消息文本（消息会随版本和语言变）
--   if (e.code === '23505') throw new BusinessError('邮箱已被注册');
--   if (e.code === '40001' || e.code === '40P01') return retryWithBackoff();

-- 顺带：能用一条语句表达的就别开事务（10 章讲过），上面这套骨架
-- 只在「多条语句必须同生共死」时才需要

-- 幂等写入：重试安全的插入
INSERT INTO events (idempotency_key, payload) VALUES ($1, $2)
ON CONFLICT (idempotency_key) DO NOTHING;

# —— 逻辑备份 ——
# 自定义格式（推荐）：已压缩，可并行恢复，可选择性恢复单表
pg_dump -Fc -d "$DATABASE_URL" -f backup.dump

# 纯 SQL 文本（可读，可直接用 psql 灌回去）
pg_dump -d "$DATABASE_URL" -f backup.sql

# 只导结构 / 只导数据 / 只导一张表
pg_dump --schema-only -d "$DATABASE_URL" -f schema.sql
pg_dump --data-only  -d "$DATABASE_URL" -f data.sql
pg_dump -t orders    -d "$DATABASE_URL" -f orders.dump -Fc

# 恢复（-j 4 并行，大库能快很多）
pg_restore -d "$DATABASE_URL" -j 4 backup.dump
psql -d "$DATABASE_URL" -f backup.sql        # 纯 SQL 用这个

# 整个实例的所有库 + 角色（pg_dump 不含角色，容易漏）
pg_dumpall -f all.sql

# —— 物理备份 ——
pg_basebackup -D /backup/base -Ft -z -P
# 再配合 postgresql.conf 里的 archive_mode / archive_command 持续归档 WAL，
# 才能做时间点恢复（PITR）。云托管数据库一般已经替你配好了。

-- PG 的 DDL 是事务性的：整个迁移要么全成，要么什么都不留
BEGIN;
  ALTER TABLE users ADD COLUMN phone text;
  CREATE INDEX idx_users_phone ON users(phone);
  UPDATE schema_version SET v = 42;
COMMIT;    -- 中途任何一步失败，ROLLBACK 后一切如初

-- 线上加索引必须用 CONCURRENTLY：不阻塞读写
CREATE INDEX CONCURRENTLY idx_orders_created ON orders(created);

-- ⚠️ 但它不能在事务里跑，报错：
--   25001  CREATE INDEX CONCURRENTLY cannot run inside a transaction block
-- 而绝大多数迁移工具默认把每个迁移包在事务里 —— 要单独标注

-- 加外键/NOT NULL 时避免全表校验阻塞：分两步
ALTER TABLE orders ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;   -- 立即返回
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;                 -- 慢，但不阻塞读写

-- 给长时间 DDL 加个保险，别让它无限期地卡住整张表
SET lock_timeout = '5s';      -- 拿不到锁就放弃，而不是排队堵死所有人

-- 装上 pg_stat_statements（需要在 postgresql.conf 里加到
-- shared_preload_libraries 并重启，云托管一般已经开好了）
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 按总耗时排：谁吃掉了数据库的时间
SELECT left(query, 60) AS q, calls,
       round(total_exec_time::numeric) AS 总毫秒,
       round(mean_exec_time::numeric, 2) AS 平均毫秒
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;
-- 按 total 排而不是 mean —— 高频的小查询才是隐形大头

-- 死元组与 autovacuum 是否跟得上
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

-- 从没被用过的索引（跑够一个业务周期再判断）
SELECT relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS 大小
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- 表和库有多大
SELECT pg_size_pretty(pg_total_relation_size('orders'));  -- 含索引
SELECT pg_size_pretty(pg_database_size(current_database()));

-- 谁在等锁（查询卡住时先看这个）
SELECT pid, state, wait_event_type, left(query, 60)
FROM pg_stat_activity WHERE wait_event_type = 'Lock';