-- 一段很「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 一行起库 ——
docker run -d --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 postgres:18

# —— 连上：psql 是官方命令行客户端 ——
psql -h localhost -U postgres                # 分开写参数
psql postgresql://postgres:secret@localhost:5432/postgres   # 或给连接串

# 放进环境变量后，裸敲 psql 就能连
export DATABASE_URL=postgresql://postgres:secret@localhost:5432/postgres
psql

# —— 连上之后最该先会的元命令（反斜杠开头，不是 SQL）——
\l              # 列出所有数据库
\c shop         # 切换到 shop 库
\dt             # 列出当前库的表
\d users        # 看 users 表：列、类型、索引、约束——最常用的一个
\di             # 列出索引
\x              # 竖排显示，列很多时救命
\timing         # 打开耗时显示，调性能时必开
\?              # 元命令帮助（\h SELECT 则是查 SQL 语法）
\q              # 退出

-- 1. 建库，然后 \c shop 切进去
CREATE DATABASE shop;

-- 2. 建表
CREATE TABLE users (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name    text NOT NULL,
  email   text UNIQUE NOT NULL,
  created timestamptz NOT NULL DEFAULT now()
);

-- 3. 插数据；RETURNING 把生成的 id 直接还给你
INSERT INTO users (name, email) VALUES
  ('Alice', 'alice@example.com'),
  ('Bob',   'bob@example.com')
RETURNING id, name;

-- 4. 查出来
SELECT id, name, email, created FROM users ORDER BY id;

-- —— 存成 schema.sql 后，一行重建 ——
-- psql 里：   \i schema.sql
-- 终端里：   psql "$DATABASE_URL" -f schema.sql
--           psql "$DATABASE_URL" -c "SELECT count(*) FROM users"   -- 跑单条并退出

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

-- 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';

-- UPSERT：插入或更新
INSERT INTO user_settings (user_id, theme, updated_at)
VALUES (1, 'dark', now())
ON CONFLICT (user_id)
DO UPDATE SET
  theme = EXCLUDED.theme,   -- EXCLUDED = 被拒绝的新值
  updated_at = EXCLUDED.updated_at;

-- 幂等插入
INSERT INTO event_log (event_id, data)
VALUES ('evt_123', '{...}')
ON CONFLICT (event_id) DO NOTHING;

-- RETURNING：插入后立即返回
INSERT INTO posts (title, author_id)
VALUES ('Hello', 1) RETURNING id, created_at;

-- MERGE（PG15+）
MERGE INTO inventory AS target
USING incoming_stock AS source
ON target.product_id = source.product_id
WHEN MATCHED THEN
  UPDATE SET quantity = target.quantity + source.quantity
WHEN NOT MATCHED THEN
  INSERT (product_id, quantity)
  VALUES (source.product_id, source.quantity);

-- 全文搜索
SELECT * FROM articles
WHERE to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))
      @@ to_tsquery('english', 'postgres & database');

-- 生产用法：生成列 + GIN 索引
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))
  ) STORED;
CREATE INDEX idx_search ON articles USING GIN(search_vector);

-- 搜索排名
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'database') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- 生成列（PG12+）
ALTER TABLE orders ADD COLUMN total_with_tax numeric
  GENERATED ALWAYS AS (total * 1.1) STORED;

-- 常用扩展
CREATE EXTENSION IF NOT EXISTS pg_trgm;    -- 模糊匹配
CREATE EXTENSION IF NOT EXISTS postgis;    -- 地理空间
CREATE EXTENSION IF NOT EXISTS vector;     -- 向量搜索（AI/RAG）
CREATE EXTENSION IF NOT EXISTS pg_cron;    -- 定时任务

-- pg_trgm 模糊搜索（容错拼写错误）
SELECT name, similarity(name, 'jonh') AS sim
FROM users WHERE name % 'jonh'
ORDER BY sim DESC;

-- 角色：可登录的用户 + 不可登录的“组”角色
CREATE ROLE app_user LOGIN PASSWORD 'secret';
CREATE ROLE readonly;                 -- 组角色，用于打包权限
GRANT readonly TO app_user;         -- 把组权限授予用户

-- GRANT / REVOKE：按对象授权
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT INSERT, UPDATE ON orders TO app_user;
REVOKE DELETE ON orders FROM app_user;

-- 行级安全（RLS）：多租户按行隔离
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::uuid);
-- 之后每个连接先 SET app.tenant_id = '...'，就只能看到本租户的行

-- 五个最常抄的写法，查到名字后照着改

-- ① 每个用户的最新一条订单（DISTINCT ON 是 PG 特有，比窗口函数简洁）
SELECT DISTINCT ON (user_id) *
FROM orders ORDER BY user_id, created DESC;

-- ② 按月汇总（date_trunc 把时间截断到月初）
SELECT date_trunc('month', created) AS 月份,
       count(*) AS 单数,
       coalesce(sum(amount), 0) AS 金额,
       count(*) FILTER (WHERE status = 'paid') AS 已支付
FROM orders GROUP BY 1 ORDER BY 1;

-- ③ UPSERT：插入或更新，并拿回结果
INSERT INTO stats (day, hits) VALUES (current_date, 1)
ON CONFLICT (day) DO UPDATE SET hits = stats.hits + EXCLUDED.hits
RETURNING *;

-- ④ 反连接：找出「没有订单的用户」
SELECT u.* FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- ⑤ keyset 分页：翻到多深都一样快
SELECT * FROM orders
WHERE (created, id) < ($1, $2)
ORDER BY created DESC, id DESC LIMIT 20;

-- timestamptz 与 timestamp 的实际区别（这是最值得记住的一条）
SET timezone = 'Asia/Shanghai';
SELECT '2026-01-01 12:00:00+00'::timestamptz;  -- 2026-01-01 20:00:00+08
SET timezone = 'UTC';
SELECT '2026-01-01 12:00:00+00'::timestamptz;  -- 2026-01-01 12:00:00+00
-- 同一个时刻，按当前时区显示 —— 存的始终是 UTC，只是显示随时区变
-- timestamp 则不带时区信息，是一个「裸墙钟读数」，换时区不会变，
-- 也无法回答「这到底是哪一刻」。所以默认一律用 timestamptz。

-- 显式转换时区
SELECT created AT TIME ZONE 'Asia/Shanghai' FROM orders;

-- 类型转换：两种写法等价，::  更常用
SELECT '42'::int, CAST('42' AS int);

-- 三个最常抄的组合

-- ① 按天汇总并补齐没有数据的日期（用 generate_series 生成完整日历再左连）
SELECT d::date AS 日期, count(o.id) AS 单数
FROM generate_series(
       current_date - '29 days'::interval, current_date, '1 day'
     ) d
LEFT JOIN orders o ON date_trunc('day', o.created) = d
GROUP BY 1 ORDER BY 1;

-- ② 把一对多聚成一行（每个用户的标签列表）
SELECT u.name, string_agg(t.name, ', ' ORDER BY t.name) AS 标签
FROM users u
JOIN user_tags ut ON ut.user_id = u.id
JOIN tags t ON t.id = ut.tag_id
GROUP BY u.id, u.name;

-- ③ 取邮箱域名并统计（split_part 比正则好读）
SELECT split_part(email, '@', 2) AS 域名, count(*)
FROM users GROUP BY 1 ORDER BY 2 DESC;

-- ~/.psqlrc：这几行值得每台机器都配
\timing on
\pset null '(null)'
\set ON_ERROR_STOP on
\set PROMPT1 '%[%033[1;31m%]%/@%M%[%033[0m%]%R%# '   -- 库名+主机名，生产库一眼认出

-- 宽表看不清？一个 \x 就够
=> \x
Expanded display is on.
=> SELECT * FROM orders LIMIT 1;
-[ RECORD 1 ]-+----------------------
id            | 1024
customer_id   | 77
total_cents   | 12800
created_at    | 2026-07-29 09:12:03+08

-- 每 2 秒刷新一次：盯长事务、盯队列
=> SELECT pid, state, now()-xact_start AS age, query
   FROM pg_stat_activity WHERE state <> 'idle' ORDER BY age DESC; \watch 2

# 脚本里一定要加 ON_ERROR_STOP，否则出错也会继续往下跑
$ psql -v ON_ERROR_STOP=1 -f migrate.sql

-- 给「人用」的账号做好限制，比事后追责有用
CREATE ROLE analyst LOGIN PASSWORD '...' CONNECTION LIMIT 5;
GRANT CONNECT ON DATABASE app TO analyst;
GRANT USAGE ON SCHEMA public TO analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analyst;

-- 顺手加一条超时，防止有人开着一个忘了关的事务
ALTER ROLE analyst SET idle_in_transaction_session_timeout = '5min';
ALTER ROLE analyst SET statement_timeout = '30s';

-- 看看现在谁连着、开着什么
SELECT usename, application_name, client_addr, state, count(*)
FROM pg_stat_activity GROUP BY 1,2,3,4 ORDER BY count DESC;

-- 自动后台这条路的安全地基：逐表启用 + 显式策略
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY own_orders ON orders
  FOR SELECT
  USING (customer_id = current_setting('request.jwt.claims', true)::json->>'sub');

-- 验证策略：必须切到受限角色，否则表所有者会绕过 RLS
SET ROLE web_anon;
SELECT count(*) FROM orders;      -- 应该只看到自己的行
RESET ROLE;

-- 检查有没有「开了 RLS 但一条策略都没写」或「根本没开」的表
SELECT c.relname,
       c.relrowsecurity  AS rls_enabled,
       count(p.polname)  AS policies
FROM pg_class c
LEFT JOIN pg_policy p ON p.polrelid = c.oid
WHERE c.relkind = 'r' AND c.relnamespace = 'public'::regnamespace
GROUP BY 1,2 ORDER BY rls_enabled, policies;