# 基本连接格式
psql -h 主机地址 -p 端口 -U 用户名 -d 数据库名

# 本机连接（默认端口 5432）
psql -U postgres -d postgres

# 指定端口（例如 5433）并要求输入密码
psql -h localhost -p 5433 -U postgres -d postgres

CREATE TABLE student (
    id      integer PRIMARY KEY,
    name    text NOT NULL,
    major   text,
    score   numeric(5, 2),
    enrolled date DEFAULT CURRENT_DATE
);

-- 单条插入
INSERT INTO student (id, name, major, score)
VALUES (1, '张三', '计算机', 88.5);

-- 多条批量插入
INSERT INTO student (id, name, major, score) VALUES
    (2, '李四', '软件工程', 92.0),
    (3, '王五', '计算机', 76.5),
    (4, '赵六', '数学', NULL);

-- 查询所有列、所有行
SELECT * FROM student;

-- 投影：只取需要的列；WHERE 过滤行
SELECT name, score FROM student WHERE major = '计算机';

-- DISTINCT 去重：有哪些专业
SELECT DISTINCT major FROM student;

-- 排序 + 分页：按分数降序，取前 2 条
SELECT name, score FROM student
ORDER BY score DESC
LIMIT 2 OFFSET 1;   -- 跳过 1 行后取 2 行

-- 更新：一定要带 WHERE，否则全表更新！
UPDATE student SET score = 80.0 WHERE id = 4;

-- 删除：同样一定要带 WHERE
DELETE FROM student WHERE name = '赵六';

-- 错误：永远查不到 score 为 NULL 的行
SELECT * FROM student WHERE score = NULL;

-- 正确
SELECT * FROM student WHERE score IS NULL;

-- 空值合并：NULL 时取默认值（COALESCE 从左到右取第一个非 NULL）
SELECT name, COALESCE(score, 0) AS score FROM student;

-- NULLIF(a, b)：a = b 时返回 NULL，常用于避免除零
SELECT score / NULLIF(score, 0) FROM student;

-- 创建 / 删除数据库（数据库级别操作，不能在事务里执行）
CREATE DATABASE shop ENCODING 'UTF8';
DROP DATABASE IF EXISTS shop;

-- 模式：给多租户、多业务模块做命名空间隔离
CREATE SCHEMA analytics;
CREATE TABLE analytics.daily_stats (id integer);  -- 全限定名
SET search_path TO analytics, public;             -- 切换默认查找路径

CREATE TABLE orders (
    order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- 主键：非空 + 唯一
    user_id     bigint NOT NULL,                                 -- 非空约束
    status      text NOT NULL DEFAULT 'pending',                 -- 默认值
    amount      numeric(10,2) CHECK (amount >= 0),              -- 检查约束
    email       text UNIQUE,                                     -- 唯一约束
    created_at  timestamptz NOT NULL DEFAULT now(),
    -- 外键：引用另一张表，级联删除
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 现代推荐：SQL 标准的 IDENTITY 列（PG 10+）
id bigint GENERATED ALWAYS AS IDENTITY
    -- ALWAYS：手动插 id 会被拒绝，更安全；BY DEFAULT 则允许手动指定

-- 传统方式：serial 伪类型（内部就是一个序列对象）
id bigserial PRIMARY KEY

-- 序列也可单独创建、手动调用
CREATE SEQUENCE order_seq START 1000;
SELECT nextval('order_seq');

-- 加列（PG 11+ 加带常量默认值的列不重写全表，秒级完成）
ALTER TABLE orders ADD COLUMN remark text DEFAULT '';

-- 删列、重命名列
ALTER TABLE orders DROP COLUMN remark;
ALTER TABLE orders RENAME COLUMN status TO state;

-- 修改类型（需要有隐式转换路径，大表会锁表重写，慎用）
ALTER TABLE orders ALTER COLUMN amount TYPE numeric(12,2);

-- 追加 / 删除约束（命名便于日后管理）
ALTER TABLE orders ADD CONSTRAINT chk_amount CHECK (amount <= 999999);
ALTER TABLE orders DROP CONSTRAINT chk_amount;

-- DROP：连表带结构、索引、约束一起删除
DROP TABLE IF EXISTS orders;

-- 若有别的对象依赖它（如视图），会报错；CASCADE 连依赖一起删（危险）
DROP TABLE orders CASCADE;

-- TRUNCATE：只清空数据、保留结构，比 DELETE 快得多（直接重写文件块）
TRUNCATE TABLE logs;

-- 多表一起清，并重置序列
TRUNCATE logs, audit RESTART IDENTITY CASCADE;

-- 普通视图：存储的是查询语句，每次查询实时计算
CREATE VIEW v_active_orders AS
SELECT order_id, user_id, amount FROM orders WHERE status = 'paid';

-- 物化视图：把结果落盘，查询快，但数据不自动更新
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT date_trunc('day', created_at) AS day, SUM(amount) AS total
FROM orders GROUP BY 1;

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;  -- 刷新但不锁读

-- 会话结束自动销毁，常用于中间结果
CREATE TEMP TABLE tmp_calc AS
SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id;

-- varchar(n)：限长，超长报错；text：不限长。性能完全相同！
-- 在 PG 里没有理由为了性能选 varchar，text 生态最好
name  varchar(50),
intro text,

-- 常用字符串函数与操作符
SELECT 'Post' || 'greSQL';               -- 拼接：PostgreSQL
SELECT upper(name), length(name) FROM student;
SELECT substr('PostgreSQL', 1, 4);       -- Post
SELECT '  hi  '::text AS trimmed;          -- 类型转换写法：值::类型
SELECT trim('  hi  ');                    -- hi
SELECT format('%s 有 %s 元', '张三', 100); -- 格式化

-- timestamp：不带时区；timestamptz：带时区（全球业务强烈推荐）
-- date：纯日期；time：纯时间；interval：时间间隔
created_at timestamptz DEFAULT now(),

SELECT now();                              -- 当前事务开始时间（事务内恒定）
SELECT clock_timestamp();                  -- 真实当前时刻
SELECT now() + INTERVAL '7 days';          -- 一周后
SELECT date_trunc('month', now());         -- 截断到月初
SELECT extract(year FROM now());           -- 提取年份

-- 时区转换
SELECT now() AT TIME ZONE 'Asia/Shanghai';

is_active boolean DEFAULT true,
-- 可取 true/false、't'/'f'、'yes'/'no'、1/0

SELECT * FROM users WHERE is_active;     -- 直接当条件用
SELECT NOT is_active FROM users;

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

ALTER TABLE orders ALTER COLUMN status TYPE order_status;

-- 好处：天然有序（可 ORDER BY / 范围比较）、非法值插不进来
-- 代价：加值需 ALTER TYPE，删值麻烦；要求灵活时可用 text + CHECK 约束替代
ALTER TYPE order_status ADD VALUE 'refunded';  -- 事务外执行

id uuid PRIMARY KEY DEFAULT gen_random_uuid(),  -- PG13+ 内置函数

-- 优点：分布式生成不冲突、暴露在 URL 不泄露业务量级
-- 缺点：16 字节大于 bigint 的 8 字节，索引更大、无序写入更碎页

CREATE TABLE article (
    id    integer PRIMARY KEY,
    title text,
    tags  text[]           -- 一维数组；也可 text[][] 多维
);

INSERT INTO article VALUES (1, 'PG 入门', ARRAY['db', 'sql', 'tutorial']);
INSERT INTO article VALUES (2, '数组玩法', '{"db", "advanced"}');

SELECT title, tags[1] FROM article;            -- 下标从 1 开始！
SELECT * FROM article WHERE 'sql' = ANY(tags); -- 数组中任一元素等于值
SELECT * FROM article WHERE tags @> ARRAY['db']; -- 包含关系

-- 数组可以建 GIN 索引，ANY 查询能走索引（第 5 章）

CREATE TABLE events (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    data  jsonb
);

INSERT INTO events (data) VALUES
('{"user": {"id": 1, "name": "张三"}, "action": "click", "tags": ["home", "banner"]}');

-- 取值：-> 返回 jsonb，->> 返回 text
SELECT data->'user'->>'name' FROM events;              -- 张三
SELECT data->'tags'->0 FROM events;                    -- "home"

-- jsonb 专属操作符
SELECT data ? 'action' FROM events;                      -- 是否存在顶层键
SELECT data['tags'] @> '["home"]' FROM events;           -- 包含
SELECT data || '{"ip": "1.2.3.4"}'::jsonb FROM events;   -- 合并
UPDATE events SET data = data - 'ip';                     -- 删键

-- 建索引：GIN 让包含查询飞起来
CREATE INDEX idx_events_data ON events USING GIN (data);
SELECT * FROM events WHERE data @> '{"action": "click"}';

CREATE TABLE users (
    id   integer PRIMARY KEY,
    name text NOT NULL,
    city text NOT NULL
);

CREATE TABLE orders (
    id      integer PRIMARY KEY,
    user_id integer REFERENCES users(id),
    amount  numeric(10,2) NOT NULL,
    status  text NOT NULL,
    created_at date NOT NULL
);

INSERT INTO users VALUES
 (1,'张三','北京'),(2,'李四','上海'),(3,'王五','北京'),(4,'赵六','广州');

INSERT INTO orders VALUES
 (101,1, 250.00,'paid',  '2026-09-01'),
 (102,1,  80.50,'pending','2026-09-02'),
 (103,2, 999.00,'paid',  '2026-09-02'),
 (104,3, 120.00,'paid',  '2026-09-03'),
 (105,3, 45.00,'closed','2026-09-04'),
 (106,4, 300.00,'paid',  '2026-09-05');

-- 模式匹配：LIKE / ILIKE；或更强大的正则（PG 特色）
SELECT * FROM users WHERE name LIKE '张%';
SELECT * FROM users WHERE name ~ '^张.{1,2}$';   -- 正则匹配

-- EXISTS 半连接：判断「存在与否」比 IN 更直观高效（尤其子查询大表时）
SELECT u.* FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'paid');

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

-- 常用聚合函数：COUNT SUM AVG MIN MAX
SELECT COUNT(*) AS 总单数, SUM(amount) AS 总金额, AVG(amount)::numeric(10,2) AS 均价
FROM orders;

-- GROUP BY：先分组再聚合；SELECT 只能出现分组列和聚合结果
SELECT u.city, COUNT(*) AS order_cnt, SUM(o.amount) AS total
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.city
ORDER BY total DESC;

-- HAVING：对聚合结果过滤（WHERE 是分组前过滤，HAVING 是分组后）
SELECT user_id, SUM(amount) AS total
FROM orders
WHERE status = 'paid'                 -- 先筛掉非已支付
GROUP BY user_id
HAVING SUM(amount) > 200              -- 再筛总额大于 200 的
ORDER BY total DESC;

-- FILTER 子句：一个查询里多种口径（PG 特色，比反复 CASE WHEN 干净）
SELECT
    COUNT(*) AS all_cnt,
    COUNT(*) FILTER (WHERE status = 'paid')    AS paid_cnt,
    SUM(amount) FILTER (WHERE status = 'pending') AS pending_amt
FROM orders;

-- LEFT JOIN + IS NULL 找「没有已支付订单」的用户（经典反连接模式）
SELECT u.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid'  -- 连接条件放 ON
WHERE o.id IS NULL;

-- 注意：LEFT JOIN 时「右表过滤条件」必须写在 ON 里；
-- 写到 WHERE 里会退化成 INNER JOIN（NULL 被 WHERE 过滤掉了）
SELECT u.name, o.id AS order_id
FROM users u LEFT JOIN orders o ON o.user_id = u.id AND o.amount > 100;

SELECT city FROM users WHERE id < 3
UNION                     -- 去重合并（UNION ALL 不去重、更快，能不用去重就 ALL）
SELECT city FROM users WHERE id > 2;

SELECT city FROM users
INTERSECT                -- 交集
SELECT u.city FROM users u JOIN orders o ON o.user_id = u.id;

SELECT city FROM users
EXCEPT                   -- 差集
SELECT '广州';

-- 标量子查询：返回单值
SELECT name FROM users
WHERE id = (SELECT user_id FROM orders ORDER BY amount DESC LIMIT 1);

-- 派生表：子查询当表用
SELECT t.city, t.total
FROM (SELECT u.city, SUM(o.amount) AS total
      FROM users u JOIN orders o ON o.user_id = u.id
      GROUP BY u.city) t
WHERE t.total > 100;

-- LATERAL：子查询引用外层列，逐行驱动（对每行用户取其金额最高的订单）
SELECT u.name, top_order.amount
FROM users u,
LATERAL (SELECT amount FROM orders o
         WHERE o.user_id = u.id
         ORDER BY amount DESC LIMIT 1) top_order;

-- 语法：函数(...) OVER (PARTITION BY 分组列 ORDER BY 排序列 frame)
SELECT
    user_id, amount, created_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn,
    RANK()       OVER (ORDER BY amount DESC)  AS rnk,   -- 并列同名次，跳号
    DENSE_RANK() OVER (ORDER BY amount DESC)  AS drnk,  -- 并列同名次，不跳号
    SUM(amount)  OVER (PARTITION BY user_id)  AS user_total,
    AVG(amount)  OVER ()                        AS global_avg,
    amount - AVG(amount) OVER (PARTITION BY user_id) AS diff_from_user_avg
FROM orders;

-- 每个用户金额最高的 2 单：GROUP BY 做不到，窗口函数轻松拿捏
SELECT * FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn
    FROM orders o
) t
WHERE rn <= 2;

-- 计算每个用户订单金额的环比变化（当前行 vs 上一单）
SELECT
    user_id, created_at, amount,
    LAG(amount)  OVER (PARTITION BY user_id ORDER BY created_at) AS prev_amt,
    amount - LAG(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS delta
FROM orders;

-- 累计求和（默认帧：分区起点到当前行）
SELECT created_at, amount,
       SUM(amount) OVER (ORDER BY created_at) AS running_total
FROM orders;

-- 普通 CTE：让复杂查询可读
WITH paid AS (
    SELECT * FROM orders WHERE status = 'paid'
),
city_total AS (
    SELECT u.city, SUM(p.amount) AS total
    FROM users u JOIN paid p ON p.user_id = u.id
    GROUP BY u.city
)
SELECT * FROM city_total WHERE total > 100 ORDER BY total DESC;

-- 递归 CTE：遍历树形结构（组织架构、评论楼层、目录树）
WITH RECURSIVE org_tree AS (
    -- 种子：根节点
    SELECT id, name, 1 AS depth
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    -- 递归：找下一层
    SELECT e.id, e.name, t.depth + 1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY depth;

-- 生成时间序列也常用递归 CTE（报表补零点）
WITH RECURSIVE days AS (
    SELECT DATE '2026-09-01' AS d
    UNION ALL
    SELECT d + 1 FROM days WHERE d < DATE '2026-09-30'
)
SELECT * FROM days;

-- 「每个用户金额最高的那一单」一行代码（其他数据库要羡慕哭）
SELECT DISTINCT ON (user_id)
       user_id, id, amount
FROM orders
ORDER BY user_id, amount DESC;

-- UPSERT：存在则更新，不存在则插入（PG 的 ON CONFLICT 扩展）
INSERT INTO orders (id, user_id, amount, status, created_at)
VALUES (101, 1, 260.00, 'paid', '2026-09-01')
ON CONFLICT (id) DO UPDATE
SET amount = EXCLUDED.amount, status = EXCLUDED.status;

-- 只在冲突时什么都不做
INSERT INTO users (id, name, city) VALUES (4, '赵六', '广州')
ON CONFLICT (id) DO NOTHING;

-- RETURNING：DML 直接返回受影响的行，省一次回查
UPDATE orders SET status = 'paid' WHERE status = 'pending'
RETURNING id, amount;

DELETE FROM orders WHERE status = 'closed'
RETURNING id;

-- 删除旧数据的同时 INSERT 归档表（同一语句原子完成）
WITH moved AS (
    DELETE FROM orders WHERE created_at < '2026-01-01' RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;

-- 最基本的创建与查看
CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_created  ON orders (created_at DESC);

-- 唯一索引（等于唯一约束的底层实现）
CREATE UNIQUE INDEX idx_users_email ON users (email);

-- 部分索引：只索引满足条件的行，写入更频繁的状态时省空间提速度
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

-- 表达式索引：查询里用了函数/表达式时，普通索引用不上！
CREATE INDEX idx_users_email_lower ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'zhang@ex.com';  -- 现在能走索引

-- 覆盖索引：把常一起查的列直接带上，免回表
CREATE INDEX idx_orders_user_amount ON orders (user_id) INCLUDE (amount);

-- 删除 / 重建（重建可回收碎片）
DROP INDEX idx_orders_user_id;
REINDEX INDEX idx_orders_user_amount;

-- GIN：jsonb / 数组 / 全文
CREATE INDEX idx_events_data ON events USING GIN (data);
CREATE INDEX idx_article_tags ON article USING GIN (tags);

-- BRIN：每天千万行的日志表，时间列自然递增 —— 索引只有几 MB
CREATE INDEX idx_logs_ts ON logs USING BRIN (ts);

-- B-tree 联合索引：最左前缀原则
-- 生效：WHERE a=?  / WHERE a=? AND b=? ；不生效：WHERE b=?
CREATE INDEX idx_multi ON orders (user_id, status, created_at);

EXPLAIN ANALYZE
SELECT u.name, SUM(o.amount)
FROM users u JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'
GROUP BY u.name;

HashAggregate  (cost=.. rows=3) (actual time=0.18..0.19 rows=3 loops=1)
  ->  Hash Join  (actual time=0.11..0.15 rows=4 loops=1)
        Hash Cond: (o.user_id = u.id)
        ->  Seq Scan on orders o  (actual time=0.02..0.06 rows=4 loops=1)
              Filter: (status = 'paid')
              Rows Removed by Filter: 2
        ->  Hash  (rows=4)
              ->  Seq Scan on users u  (rows=4)

-- pg_stat_statements：全局慢查询统计（postgresql.conf 里 preload 后 CREATE EXTENSION）
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT calls, round(total_exec_time::numeric, 1) AS total_ms, left(query, 60)
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 键集分页（keyset pagination）：深分页时比 OFFSET 快几个量级
SELECT * FROM orders
WHERE created_at < '2026-09-03'   -- 上一页最后一行的值
ORDER BY created_at DESC
LIMIT 20;

VACUUM orders;              -- 回收死元组空间（可复用，不归还 OS），不锁读写
VACUUM FULL orders;         -- 重写整表、彻底归还空间 —— 锁表！线上错峰执行
ANALYZE orders;             -- 更新统计信息（不清理空间，只刷新数据分布）

-- 日常交给 autovacuum 后台进程（默认开启，别关）
-- 查看表的死元组情况
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

BEGIN;                              -- 或 START TRANSACTION，开启事务
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;                             -- 提交：两步同时生效

-- 出问题了？回滚：
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
ROLLBACK;                           -- 一切恢复原样

-- SAVEPOINT：事务里的小抄本，可局部回滚
BEGIN;
INSERT INTO orders (id) VALUES (1);
SAVEPOINT sp1;
INSERT INTO orders (id) VALUES (2);
ROLLBACK TO sp1;                    -- 只撤销第 2 条
COMMIT;                             -- 第 1 条仍保留

-- 设置事务隔离级别（推荐每个事务级别设置）
BEGIN ISOLATION LEVEL SERIALIZABLE;
...
COMMIT;

-- 也可以会话级设置
SET default_transaction_isolation = 'repeatable read';

-- SERIALIZABLE 下检测到冲突会直接报错回滚，应用需捕获后重试：
-- ERROR: could not serialize access due to read/write dependencies
-- among transactions（SQLSTATE 40001 —— 重试即可）

-- 亲眼看看隐藏列
SELECT xmin, xmax, * FROM orders WHERE id = 101;

-- 显式行锁：SELECT ... FOR UPDATE（改前锁定，防丢失更新）
-- 两个事务同时执行下面语句时，后到者会阻塞等待
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;   -- 行锁
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- FOR UPDATE：锁定并等待；NOWAIT：锁不到立即报错；SKIP LOCKED：跳过（任务队列神器）
SELECT * FROM jobs WHERE status = 'pending'
ORDER BY id LIMIT 1
FOR UPDATE SKIP LOCKED;     -- 多 worker 抢任务互不冲突

-- 表锁（锁住整张表，谨慎使用）
LOCK TABLE orders IN EXCLUSIVE MODE;

-- 咨询锁（advisory lock）：应用层自定义锁，不绑任何表
SELECT pg_advisory_lock(12345);
SELECT pg_advisory_unlock(12345);

-- 监控超过 1 分钟的空闲事务（配合 idle_in_transaction_session_timeout 自动终止）
SELECT pid, usename, state, now() - xact_start AS xact_age,
       now() - state_change AS idle_time, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > INTERVAL '1 minute'
ORDER BY xact_age DESC;

-- 紧急终止某个卡死的会话
SELECT pg_terminate_backend(pid);

-- 创建可登录的用户角色
CREATE ROLE app_user LOGIN PASSWORD 'StrongPass!123';

-- 创建「组角色」，不登录、只作为权限集合
CREATE ROLE read_only NOLOGIN;

-- 角色属性速查
CREATE ROLE dev_admin LOGIN CREATEDB;           -- 可建数据库
CREATE ROLE super_x LOGIN SUPERUSER;            -- 超级用户（慎用，等同 root）
CREATE ROLE limited LOGIN CONNECTION LIMIT 10;   -- 限制并发连接数
ALTER ROLE app_user VALID UNTIL '2026-12-31';   -- 密码有效期

-- 用户加入组：获得组的所有权限
GRANT read_only TO app_user;

-- 查看现有角色与属性
\du
SELECT rolname, rolcanlogin, rolsuper FROM pg_roles;

-- 库级：连接权限
GRANT CONNECT ON DATABASE shop TO app_user;

-- 模式级：能「看见」并使用该模式
GRANT USAGE ON SCHEMA public TO app_user;

-- 表级：增删改查
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;   -- 模式下所有表

-- 只开放部分列（如隐藏手机号）
GRANT SELECT (id, name, city) ON users TO read_only;

-- 序列也要授权，否则 INSERT 会失败（自增主键取号需要）
GRANT USAGE, SELECT ON SEQUENCE orders_id_seq TO app_user;

-- 回收
REVOKE DELETE ON orders FROM app_user;

-- 默认权限：让「将来新建的表」自动授权（新表免手工 GRANT）
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public
GRANT SELECT ON TABLES TO read_only;

-- 查看某张表的授权情况
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'orders';

-- PUBLIC 是「所有角色的隐式组」。以下收走 PUBLIC 的默认权限（多租户环境常用加固）
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE shop FROM PUBLIC;

-- 1. 建表 + 打开 RLS
CREATE TABLE tenant_data (
    id       integer PRIMARY KEY,
    tenant   text NOT NULL,          -- 租户标识
    content  text
);
ALTER TABLE tenant_data ENABLE ROW LEVEL SECURITY;

-- 2. 建立策略：每个用户只能看到自己租户的行
CREATE POLICY tenant_isolation ON tenant_data
    USING (tenant = current_user);            -- 读：过滤
--  更完整的语法：USING（读）+ WITH CHECK（写校验）
CREATE POLICY tenant_rw ON tenant_data
    FOR ALL
    USING (tenant = current_setting('app.tenant'))
    WITH CHECK (tenant = current_setting('app.tenant'));

-- 3. 应用侧设置会话变量（连接池里按请求设置）
SET app.tenant = 'company_a';
SELECT * FROM tenant_data;    -- 自动只返回 company_a 的行！

-- 表拥有者、超级用户默认不受 RLS 约束；需要约束他们时：
ALTER TABLE tenant_data FORCE ROW LEVEL SECURITY;

# 四种格式：p 纯 SQL（默认）/ c 自定义压缩 / d 目录 / t tar
# 推荐 -Fc：压缩、支持 pg_restore 并行与选择性恢复
pg_dump -h localhost -p 5432 -U postgres -Fc -f shop_20260917.dump shop

# 只备份指定表
pg_dump -Fc -t orders -t users -f subset.dump shop

# pg_dumpall：备份「全部数据库」+ 全局对象（角色、表空间）——pg_dump 不含这些！
pg_dumpall -U postgres --globals-only > globals.sql
pg_dumpall -U postgres > all_databases.sql

# 自定义格式用 pg_restore（-j 并行提速，多核恢复快数倍）
createdb shop_new                        # 先建好空库
pg_restore -U postgres -d shop_new -j 4 shop_20260917.dump

# 恢复单张表到指定库（自定义格式的杀手锏）
pg_restore -d shop_new -t orders shop_20260917.dump

# 纯 SQL 格式直接喂给 psql
psql -U postgres -d shop_new < shop_backup.sql

# 恢复全局对象（角色、密码）
psql -U postgres -f globals.sql

# 前提：postgresql.conf 开启复制连接（在 pg_hba.conf 放行 replication）
# 基础备份：-Xs 一并打包 WAL，-P 显示进度，-Fp 平铺文件格式
pg_basebackup -h localhost -p 5432 -U replicator \
    -D /backup/base_20260917 -Fp -Xs -P

# 产物直接就是一份可启动的数据目录：
# 恢复 = 停库 → 用备份目录替换数据目录 → 启动

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/%f'    # %p WAL 源路径，%f 文件名
max_wal_senders = 3

# 1) 停库，用基础备份替换数据目录
# 2) 数据目录下创建 recovery.signal（PG12+）
touch /var/lib/postgresql/18/main/recovery.signal

# 3) postgresql.conf（或 recovery 相关配置）指定归档位置与目标时间
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-09-17 15:30:00+08'

# 4) 启动数据库 —— 自动重放 WAL 到目标时刻并暂停
# 5) 确认数据无误后：SELECT pg_wal_replay_resume();  正常对外服务

# Debian / Ubuntu（需先配置 PGDG 源）
sudo apt install postgresql-17-pgvector

# RHEL / CentOS
sudo yum install pgvector_17

1) 从 pgvector 的 GitHub Releases 下载对应 PG 大版本的 Windows 构建包
   （或用 Visual Studio 开发命令行 + nmake 自行编译）
2) 复制文件到 PG 安装目录：
   vector.dll        -> D:\env\postgresql\18.6.3\lib\
   vector.control    -> D:\env\postgresql\18.6.3\share\extension\
   vector--0.8.0.sql -> D:\env\postgresql\18.6.3\share\extension\
3) 重启 PostgreSQL 服务（DLL 被加载锁定，必须先 Stop 再复制）
4) psql 连接后启用扩展

-- 每个需要使用的数据库执行一次；超级用户或有 CREATE 权限即可
CREATE EXTENSION vector;

-- 验证安装成功
SELECT extname, extversion FROM pg_extension WHERE extname = 'vector';
--  vector | 0.8.0

-- n 是维度，必须与你的 Embedding 模型一致（写死后插入不同维度会报错）
-- 常见模型维度：OpenAI text-embedding-3-small = 1536；bge-m3 = 1024
CREATE TABLE documents (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    content    text NOT NULL,             -- 原文
    embedding  vector(1536)              -- 向量列
);

-- 字面量：方括号内逗号分隔
INSERT INTO documents (content, embedding)
VALUES ('PostgreSQL 入门教程', '[0.1, 0.2, 0.3, ...]');   -- 实际 1536 个数

-- 类型转换
SELECT '[1,2,3]'::vector(3);

-- 维度查询与常用操作
SELECT embedding::text FROM documents LIMIT 1;
SELECT array_length(string_to_array(embedding::text, ','), 1) AS dims FROM documents LIMIT 1;

-- 三大距离运算符（值越小 = 越相似）
-- <->  欧氏距离（L2）：最直观的"空间距离"，通用
-- <=>  余弦距离：1 - cos(θ)，文本语义检索最常用（对向量长度不敏感）
-- <#>  负内积：向量已归一化时最快，结果等价余弦排序

-- 精确检索（无索引时全表扫描，适合小数据量）
SELECT content, embedding <-> '[0.1, 0.2, ...]'::vector AS distance
FROM documents
ORDER BY embedding <-> '[0.1, 0.2, ...]'::vector
LIMIT 5;

-- 相似度（1 - 余弦距离）
SELECT 1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
FROM documents ORDER BY similarity DESC LIMIT 5;

-- HNSW 索引：为余弦距离建（运算符要与查询一致！）
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 200);
--   m：每节点连接数（默认16，越大召回越高、内存越大）
--   ef_construction：建索引时的搜索宽度（默认64，越大质量越好、建得越慢）

-- 对应三种距离的 opclass：
--   vector_cosine_ops   配 <=>
--   vector_l2_ops       配 <->
--   ipops：vector_ip_ops  配 <#>

-- IVFFlat 索引：lists 经验值 = 表行数 / 1000（百万级数据取 1000 左右）
CREATE INDEX ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

-- 半精度列建 halfvec 索引（0.7+）：空间减半
CREATE INDEX ON documents
USING hnsw ((embedding::halfvec(1536)) halfvec_cosine_ops);

-- HNSW：增大 ef_search 提高召回（默认 40）
SET hnsw.ef_search = 100;

-- IVFFlat：增大 probes 探测更多聚类桶，提高召回（默认 1，往往太小）
SET ivfflat.probes = 10;

-- 验证召回率：对比"走索引的近似结果"与"精确结果"的重合度
SET enable_seqscan = off;   -- 强制走索引测试
SELECT id FROM documents ORDER BY embedding <=> '[...]'::vector LIMIT 20;

-- 1) 建表 + 索引
CREATE TABLE kb_chunks (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    doc_title  text,
    chunk      text NOT NULL,          -- 切块后的文本（建议 200-500 字/块）
    embedding  vector(1536),
    created_at timestamptz DEFAULT now()
);

-- 2) 灌数据 + 建索引（先灌后建，IVFFlat 才准）
-- INSERT INTO kb_chunks (doc_title, chunk, embedding) VALUES (...);
CREATE INDEX ON kb_chunks USING hnsw (embedding vector_cosine_ops);

-- 3) 语义检索：给定问题向量，找最相似的 5 个知识块
SELECT doc_title, chunk,
       1 - (embedding <=> $1::vector) AS similarity
FROM kb_chunks
ORDER BY embedding <=> $1::vector
LIMIT 5;

-- 4) 向量 + 业务条件组合：pgvector 的核心优势（专业向量库做不到或很别扭）
--    "只在已发布、属于我可见范围的文章里做语义检索"
SELECT doc_title, chunk
FROM kb_chunks c
JOIN docs d ON d.id = c.doc_id
WHERE d.status = 'published'
  AND d.owner = current_user
ORDER BY c.embedding <=> $1::vector
LIMIT 5;

-- 5) 0.8+ 的迭代式索引扫描：WHERE 过滤掉大半时，先 ANN 多取再过滤，仍走索引
SET hnsw.iterative_scan = relaxed_order;

import psycopg2
from openai import OpenAI          # 或本地模型（ollama、sentence-transformers）

client = OpenAI()

def embed(text: str) -> list[float]:
    resp = client.embeddings.create(
        model="text-embedding-3-small", input=text)
    return resp.data[0].embedding   # 1536 维

conn = psycopg2.connect(host="localhost", port=5433,
                        dbname="ragdb", user="postgres", password="***")

# 入库：文档切块 + 向量化
chunks = split_into_chunks(markdown_text, size=400)
with conn.cursor() as cur:
    for c in chunks:
        cur.execute(
            "INSERT INTO kb_chunks (doc_title, chunk, embedding) VALUES (%s,%s,%s)",
            (title, c, str(embed(c))))

# 检索：问题向量化 + 相似度查询
question = "PostgreSQL 怎么做时间点恢复？"
with conn.cursor() as cur:
    cur.execute(
        """SELECT chunk, 1 - (embedding <=> %s::vector) AS sim
           FROM kb_chunks
           ORDER BY embedding <=> %s::vector
           LIMIT 5""",
        (str(embed(question)),) * 2)
    top_chunks = cur.fetchall()

# 把 top_chunks 拼进 Prompt，交给 LLM 生成最终回答（RAG 的 R 部分）
context = "\n---\n".join(row[0] for row in top_chunks)

-- CLIP 模型：文本和图片映射到同一向量空间 → 文搜图天然可行
CREATE TABLE images (
    id          bigint PRIMARY KEY,
    url         text,
    text_vec    vector(512),    -- 用文字描述的向量（或查询时实时算）
    image_vec   vector(512)     -- 图片向量
);

-- "落日下的海滩" 搜图：文本向量 与 图片空间比对
SELECT url, image_vec <=> $1::vector AS distance
FROM images
ORDER BY image_vec <=> $1::vector   -- $1 = embed("落日下的海滩")
LIMIT 20;

-- item2vec / 双塔召回：商品、用户都向量化
-- "看过 A 的用户还看了什么" = 查 A 的向量近邻，再叠加业务过滤
SELECT p.id, p.title
FROM products p
WHERE p.id != $1
  AND p.status = 'on_sale'
  AND p.stock > 0
ORDER BY p.embedding <=> (SELECT embedding FROM products WHERE id = $1)
LIMIT 50;

-- 用户长期兴趣 = 近 30 天行为向量的加权平均 → 再查近邻
-- 这类"向量 + 业务条件"的混合查询正是 pgvector 的主场

-- 新文章入库前先查相似度，> 0.95 判定为重复/洗稿
SELECT id, title, 1 - (embedding <=> $1::vector) AS sim
FROM articles
ORDER BY embedding <=> $1::vector
LIMIT 1;
-- sim > 0.95 → 进入人工审核队列