PostgreSQL 18 知识文档

# 拉取 PostgreSQL 18 官方镜像（Debian 版本）
docker pull postgres:18-bookworm

# 拉取 Alpine 版本（更小）
docker pull postgres:18-alpine

docker run -d \
  --name pg18 \
  -e POSTGRES_PASSWORD=your_password \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  -v pg18_data:/var/lib/postgresql/data \
  postgres:18-bookworm

docker run -d \
  --name pg18 \
  -e POSTGRES_PASSWORD=your_password \
  -p 5432:5432 \
  -v /home/z/pgdata:/var/lib/postgresql/data \
  postgres:18-bookworm

version: "3.9"
services:
  postgres:
    image: postgres:18-bookworm
    container_name: pg18
    environment:
      POSTGRES_PASSWORD: your_password
      POSTGRES_DB: mydb
    ports:
      - "5432:5432"
    volumes:
      - pg18_data:/var/lib/postgresql/data
    restart: unless-stopped

  pgadmin:
    image: dpage/pgadmin4:latest
    container_name: pgadmin
    environment:
      PGADMIN_DEFAULT_EMAIL: admin@example.com
      PGADMIN_DEFAULT_PASSWORD: admin_password
    ports:
      - "8080:80"
    volumes:
      - pgadmin_data:/var/lib/pgadmin
    restart: unless-stopped

volumes:
  pg18_data:
  pgadmin_data:

# 进入 psql 终端
docker exec -it pg18 psql -U postgres

# 执行单条 SQL
docker exec -it pg18 psql -U postgres -c "SELECT version();"

-- 查看当前 search_path
SHOW search_path;

-- 临时修改（仅当前会话有效）
SET search_path TO my_schema, public;

-- 永久修改（针对角色）
ALTER ROLE myuser SET search_path TO my_schema, public;

-- 基本语法
CREATE DATABASE db_name;

-- 完整语法（指定属主、模板、编码、排序规则、连接限制）
CREATE DATABASE mydb
  WITH OWNER = myuser
       TEMPLATE = template1
       ENCODING = 'UTF8'
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8'
       CONNECTION LIMIT = 100;

-- 重命名
ALTER DATABASE mydb RENAME TO newdb;

-- 修改属主
ALTER DATABASE mydb OWNER TO newuser;

-- 修改连接限制
ALTER DATABASE mydb CONNECTION LIMIT 50;

-- 修改配置参数（针对该数据库）
ALTER DATABASE mydb SET work_mem = '64MB';
ALTER DATABASE mydb SET timezone = 'Asia/Shanghai';

-- 删除空数据库
DROP DATABASE mydb;

-- 强制删除（即使有连接，PG 13+）
DROP DATABASE mydb WITH (FORCE);

-- 基本语法
CREATE SCHEMA my_schema;

-- 指定属主
CREATE SCHEMA my_schema AUTHORIZATION myuser;

-- 在创建模式的同时创建其中的对象
CREATE SCHEMA my_schema
  CREATE TABLE t1 (id serial PRIMARY KEY)
  CREATE TABLE t2 (id serial PRIMARY KEY);

-- 重命名
ALTER SCHEMA my_schema RENAME TO new_schema;

-- 修改属主
ALTER SCHEMA my_schema OWNER TO newuser;

-- 仅删除空模式
DROP SCHEMA my_schema;

-- 级联删除模式及其下所有对象
DROP SCHEMA my_schema CASCADE;

GRANT ALL ON SCHEMA public TO PUBLIC;

-- 列出所有数据库
\l

-- 列出当前数据库下所有模式
\dn

-- 列出当前数据库下所有表
\dt

-- 列出指定模式下的表
\dt my_schema.*

-- 使用 SQL 查询所有数据库
SELECT datname FROM pg_database WHERE datistemplate = false;

-- 查询当前数据库下所有模式
SELECT schema_name FROM information_schema.schemata;

-- 查询某模式下的所有表
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'my_schema';

CREATE TABLE employees (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        text NOT NULL,
    email       text UNIQUE,
    salary      numeric(10, 2) CHECK (salary > 0),
    dept_id     bigint REFERENCES departments(id),
    created_at  timestamptz DEFAULT now(),
    metadata    jsonb DEFAULT '{}'::jsonb
);

-- 推荐方式：标识列
CREATE TABLE t1 (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    -- GENERATED ALWAYS 表示不允许手动插入 id
    -- GENERATED BY BY DEFAULT 表示手动插入时使用用户值，否则自动生成
    name text
);

-- 旧方式：SERIAL（仍兼容，但不推荐新项目使用）
CREATE TABLE t2 (
    id serial PRIMARY KEY,
    name text
);

-- 添加列
ALTER TABLE employees ADD COLUMN phone text;

-- 添加带默认值的列（PG 11+ 不锁表，瞬时完成）
ALTER TABLE employees ADD COLUMN status text DEFAULT 'active';

-- 删除列
ALTER TABLE employees DROP COLUMN phone;

-- 删除列并级联删除依赖对象
ALTER TABLE employees DROP COLUMN phone CASCADE;

-- 重命名列
ALTER TABLE employees RENAME COLUMN name TO full_name;

-- 修改列类型
ALTER TABLE employees ALTER COLUMN salary TYPE numeric(12, 2);

-- 设置/删除默认值
ALTER TABLE employees ALTER COLUMN status SET DEFAULT 'active';
ALTER TABLE employees ALTER COLUMN status DROP DEFAULT;

-- 设置/删除非空约束
ALTER TABLE employees ALTER COLUMN name SET NOT NULL;
ALTER TABLE employees ALTER COLUMN name DROP NOT NULL;

-- 添加约束
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);

-- 删除约束
ALTER TABLE employees DROP CONSTRAINT employees_email_key;

-- 删除表
DROP TABLE employees;

-- 级联删除依赖对象（如外键引用、视图）
DROP TABLE employees CASCADE;

-- 仅在存在时删除
DROP TABLE IF EXISTS employees;

-- 临时表
CREATE TEMP TABLE temp_stats AS SELECT * FROM employees WHERE dept_id = 1;

-- UNLOGGED 表
CREATE UNLOGGED TABLE cache_data (
    key text PRIMARY KEY,
    value jsonb
);

-- 创建表空间
CREATE TABLESPACE fastspace LOCATION '/mnt/ssd/pgdata';

-- 建表时指定表空间
CREATE TABLE big_table (id bigint, data text) TABLESPACE fastspace;

-- 修改表的表空间
ALTER TABLE big_table SET TABLESPACE fastspace;

CREATE TABLE cities (
    name text,
    population bigint,
    altitude float
);

CREATE TABLE capitals (
    state char(2)
) INHERITS (cities);

-- 基本插入
INSERT INTO employees (name, email, salary, dept_id)
VALUES ('Alice', 'alice@example.com', 8000, 1);

-- 多行插入
INSERT INTO employees (name, email, salary, dept_id)
VALUES
    ('Bob', 'bob@example.com', 7500, 1),
    ('Carol', 'carol@example.com', 9000, 2),
    ('Dave', 'dave@example.com', 8500, 2);

-- 插入查询结果
INSERT INTO employees_archive
SELECT * FROM employees WHERE created_at < '2024-01-01';

-- 仅插入冲突时不报错（DO NOTHING）
INSERT INTO employees (id, name) VALUES (1, 'Alice')
ON CONFLICT DO NOTHING;

-- 冲突时更新（UPSERT）
INSERT INTO employees (id, name, salary)
VALUES (1, 'Alice', 9000)
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name,
    salary = EXCLUDED.salary;

-- 基本更新
UPDATE employees SET salary = 9500 WHERE name = 'Alice';

-- 多列更新
UPDATE employees SET salary = salary * 1.1, metadata = metadata || '{"raised": true}'::jsonb
WHERE dept_id = 1;

-- 基于子查询更新
UPDATE employees SET salary = sub.avg_salary
FROM (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
) sub
WHERE employees.dept_id = sub.dept_id;

-- 基于另一个表更新
UPDATE employees e
SET salary = d.budget_salary
FROM departments d
WHERE e.dept_id = d.id;

-- RETURNING 返回更新后的行
UPDATE employees SET salary = salary * 1.1
WHERE dept_id = 1
RETURNING id, name, salary;

-- 基本删除
DELETE FROM employees WHERE name = 'Alice';

-- 基于子查询删除
DELETE FROM employees
WHERE dept_id IN (
    SELECT id FROM departments WHERE name = 'Sales'
);

-- 使用 USING 连接其他表
DELETE FROM employees e
USING departments d
WHERE e.dept_id = d.id AND d.name = 'Sales';

-- RETURNING 返回删除的行
DELETE FROM employees
WHERE created_at < '2023-01-01'
RETURNING id, name;

-- 清空表
TRUNCATE TABLE employees;

-- 级联清空（同时清空外键引用的表）
TRUNCATE TABLE departments CASCADE;

-- 重启标识列
TRUNCATE TABLE employees RESTART IDENTITY;

-- 多表同时清空
TRUNCATE TABLE employees, departments;

MERGE INTO employees AS e
USING new_employees AS n
ON e.id = n.id
WHEN MATCHED AND n.salary > e.salary THEN
    UPDATE SET salary = n.salary
WHEN MATCHED AND n.deleted = true THEN
    DELETE
WHEN NOT MATCHED THEN
    INSERT (id, name, salary) VALUES (n.id, n.name, n.salary);

-- 导出为 CSV
COPY employees TO '/tmp/employees.csv' WITH CSV HEADER;

-- 从 CSV 导入
COPY employees (name, email, salary) FROM '/tmp/employees.csv' WITH CSV HEADER;

-- 客户端 COPY（\copy，使用客户端文件系统权限）
\copy employees TO '/tmp/employees.csv' WITH CSV HEADER

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (name text, current_mood mood);

CREATE TABLE events (id bigint, data jsonb);
INSERT INTO events VALUES (1, '{"user": "alice", "tags": ["a", "b"]}'::jsonb);

-- 查询 JSON 字段
SELECT data->'user' FROM events;        -- 返回 jsonb
SELECT data->>'user' FROM events;       -- 返回 text
SELECT data->'tags'->0 FROM events;     -- 返回 jsonb 数组第一个元素

-- JSONB 包含查询
SELECT * FROM events WHERE data @> '{"user": "alice"}';

CREATE TABLE posts (
    id bigint,
    tags text[],
    scores integer[]
);

INSERT INTO posts VALUES (1, ARRAY['postgres', 'db'], ARRAY[90, 85]);

-- 数组查询
SELECT * FROM posts WHERE 'postgres' = ANY(tags);
SELECT * FROM posts WHERE tags && ARRAY['postgres'];  -- 数组有交集

-- 列级
CREATE TABLE t1 (id bigint PRIMARY KEY, name text);

-- 表级（复合主键）
CREATE TABLE t2 (
    a bigint,
    b bigint,
    PRIMARY KEY (a, b)
);

CREATE TABLE orders (
    id bigint PRIMARY KEY,
    user_id bigint REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
    total numeric(10, 2)
);

CREATE TABLE users (
    id bigint PRIMARY KEY,
    email text UNIQUE,
    username text,
    CONSTRAINT uk_users_username UNIQUE (username)
);

CREATE TABLE products (
    id bigint PRIMARY KEY,
    price numeric(10, 2) CHECK (price > 0),
    stock integer CHECK (stock >= 0),
    -- 多列检查
    CONSTRAINT chk_price_stock CHECK (price > 0 AND stock >= 0)
);

CREATE TABLE t1 (
    name text NOT NULL,
    email text  -- 默认允许 NULL
);

CREATE TABLE bookings (
    id bigint PRIMARY KEY,
    room_id integer,
    during tstzrange,
    EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

CREATE TABLE t1 (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    created_at timestamptz DEFAULT now(),
    status text DEFAULT 'pending',
    score integer DEFAULT 0
);

-- PG 18 默认 VIRTUAL（不存储）
CREATE TABLE products (
    id bigint PRIMARY KEY,
    price numeric(10, 2),
    quantity integer,
    -- 虚拟生成列：读取时计算
    total_price numeric GENERATED ALWAYS AS (price * quantity) VIRTUAL
);

-- 显式指定 STORED
CREATE TABLE products_stored (
    id bigint PRIMARY KEY,
    price numeric(10, 2),
    quantity integer,
    total_price numeric GENERATED ALWAYS AS (price * quantity) STORED
);

COMMENT ON TABLE employees IS '员工信息表';
COMMENT ON COLUMN employees.salary IS '员工月薪，单位元';
COMMENT ON DATABASE mydb IS '生产环境主库';

SELECT [DISTINCT | DISTINCT ON (expr)] column_list
FROM table_name
[WHERE condition]
[GROUP BY column_list]
[HAVING condition]
[ORDER BY column_list [ASC | DESC]]
[LIMIT n] [OFFSET m]
[FETCH FIRST n ROWS ONLY];

-- 选择所有列
SELECT * FROM employees;

-- 选择指定列
SELECT name, salary FROM employees;

-- 列别名（AS 可省略）
SELECT name AS employee_name, salary * 12 AS annual_salary FROM employees;

-- 表达式
SELECT name, salary, salary * 1.1 AS new_salary FROM employees;

-- 字符串拼接（|| 操作符 或 concat 函数）
SELECT name || ' (' || email || ')' AS info FROM employees;
SELECT concat(name, ' (', email, ')') AS info FROM employees;

SELECT * FROM employees WHERE salary > 8000;
SELECT * FROM employees WHERE dept_id IS DISTINCT FROM 1;  -- 包含 NULL 情况

SELECT * FROM employees
WHERE (dept_id = 1 OR dept_id = 2) AND salary > 8000;

SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000;
SELECT * FROM employees WHERE dept_id IN (1, 2, 3);

SELECT * FROM employees WHERE name LIKE 'A%';        -- 以 A 开头
SELECT * FROM employees WHERE name ILIKE 'a%';       -- 不区分大小写
SELECT * FROM employees WHERE name ~ '^A[a-z]+$';    -- POSIX 正则
SELECT * FROM employees WHERE email ~* '@example\.com$';

SELECT * FROM employees WHERE email IS NULL;
SELECT * FROM employees WHERE email IS NOT NULL;
SELECT * FROM employees WHERE email IS [NOT] DISTINCT FROM NULL;

-- 升序（默认）
SELECT * FROM employees ORDER BY salary;

-- 降序
SELECT * FROM employees ORDER BY salary DESC;

-- 多列排序
SELECT * FROM employees ORDER BY dept_id ASC, salary DESC;

-- 使用表达式排序
SELECT * FROM employees ORDER BY salary * 12 DESC;

-- NULL 处理
SELECT * FROM employees ORDER BY email DESC NULLS LAST;  -- NULL 排在最后
SELECT * FROM employees ORDER BY email ASC NULLS FIRST;  -- NULL 排在最前

-- 取前 10 条
SELECT * FROM employees LIMIT 10;

-- 跳过 20 条，取 10 条（分页）
SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 20;

-- SQL 标准语法（PG 18 推荐）
SELECT * FROM employees ORDER BY id
OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;

-- 基本去重
SELECT DISTINCT dept_id FROM employees;

-- 多列去重
SELECT DISTINCT dept_id, status FROM employees;

-- DISTINCT ON：每组取第一行（PG 特色）
SELECT DISTINCT ON (dept_id) dept_id, name, salary
FROM employees
ORDER BY dept_id, salary DESC;

-- 简单 CASE
SELECT name,
       CASE dept_id
           WHEN 1 THEN 'Engineering'
           WHEN 2 THEN 'Sales'
           ELSE 'Other'
       END AS dept_name
FROM employees;

-- 搜索 CASE
SELECT name, salary,
       CASE
           WHEN salary >= 10000 THEN 'High'
           WHEN salary >= 5000 THEN 'Medium'
           ELSE 'Low'
       END AS salary_level
FROM employees;

SELECT
    COUNT(*) AS total_count,
    COUNT(email) AS email_count,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    SUM(salary) AS total_salary,
    STRING_AGG(name, ', ' ORDER BY name) AS all_names,
    ARRAY_AGG(name ORDER BY salary DESC) AS name_array
FROM employees;

-- 按部门分组统计
SELECT dept_id,
       COUNT(*) AS emp_count,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
ORDER BY avg_salary DESC;

-- 多列分组
SELECT dept_id, status,
       COUNT(*) AS cnt
FROM employees
GROUP BY dept_id, status;

-- GROUP BY 与表达式
SELECT DATE_TRUNC('month', created_at) AS month,
       COUNT(*) AS new_hires
FROM employees
GROUP BY month
ORDER BY month;

SELECT dept_id, COUNT(*) AS cnt, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 5 AND AVG(salary) > 8000
ORDER BY avg_sal DESC;

-- ROLLUP：先按 (dept, status) 分组，再按 dept，最后总计
SELECT dept_id, status, COUNT(*) AS cnt
FROM employees
GROUP BY ROLLUP (dept_id, status);

-- CUBE：所有维度组合
SELECT dept_id, status, COUNT(*) AS cnt
FROM employees
GROUP BY CUBE (dept_id, status);

-- GROUPING SETS：自定义维度组合
SELECT dept_id, status, COUNT(*) AS cnt
FROM employees
GROUP BY GROUPING SETS ((dept_id, status), (dept_id), ());

-- 排名函数
SELECT name, dept_id, salary,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn,
       RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dense_rank,
       PERCENT_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS pct_rank,
       NTILE(4) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS quartile
FROM employees;

-- 累计求和
SELECT name, salary,
       SUM(salary) OVER (ORDER BY id) AS running_total,
       AVG(salary) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;

-- 部门内排名与占比
SELECT name, dept_id, salary,
       salary * 1.0 / SUM(salary) OVER (PARTITION BY dept_id) AS salary_pct
FROM employees;

-- 与上一行比较
SELECT name, salary,
       LAG(salary, 1, 0) OVER (ORDER BY id) AS prev_salary,
       salary - LAG(salary, 1, 0) OVER (ORDER BY id) AS diff
FROM employees;

-- 计算移动平均（当前行及前两行）
SELECT name, salary,
       AVG(salary) OVER (
           ORDER BY id
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg_3
FROM employees;

-- 标量子查询（返回单值）
SELECT name, salary,
       (SELECT AVG(salary) FROM employees) AS company_avg
FROM employees;

-- 表子查询（在 FROM 中）
SELECT dept_id, avg_sal
FROM (
    SELECT dept_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY dept_id
) sub
WHERE avg_sal > 8000;

-- EXISTS / NOT EXISTS
SELECT name FROM employees e
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = e.id
);

-- ANY / ALL
SELECT * FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 1);

-- 取每个部门薪资最高的 3 名员工
SELECT d.name AS dept, e.name, e.salary
FROM departments d,
LATERAL (
    SELECT name, salary
    FROM employees
    WHERE dept_id = d.id
    ORDER BY salary DESC
    LIMIT 3
) e;

-- 长度
SELECT length('hello');          -- 5（字符数）
SELECT char_length('hello');     -- 5（字符数）
SELECT octet_length('你好');     -- 6（字节数，UTF-8 中文 3 字节）

-- 大小写转换
SELECT upper('hello');           -- 'HELLO'
SELECT lower('HELLO');           -- 'hello'
SELECT initcap('hello world');   -- 'Hello World'

-- 拼接
SELECT 'a' || 'b';               -- 'ab'
SELECT concat('a', 'b', 'c');    -- 'abc'（忽略 NULL）
SELECT concat_ws(',', 'a', 'b'); -- 'a,b'（带分隔符）

-- 截取
SELECT substring('hello' FROM 2 FOR 3);  -- 'ell'
SELECT substr('hello', 2, 3);            -- 'ell'
SELECT left('hello', 3);                 -- 'hel'
SELECT right('hello', 3);                -- 'llo'

-- 查找与替换
SELECT position('l' IN 'hello');         -- 3
SELECT strpos('hello', 'l');             -- 3
SELECT replace('hello', 'l', 'L');       -- 'heLLo'
SELECT translate('hello', 'el', 'ip');   -- 'hippo'

-- 填充
SELECT lpad('5', 3, '0');                -- '005'
SELECT rpad('5', 3, '0');                -- '500'

-- 去除空白
SELECT trim('  hello  ');                -- 'hello'
SELECT ltrim('  hello');                 -- 'hello'
SELECT rtrim('hello  ');                 -- 'hello'

-- 分割
SELECT split_part('a,b,c', ',', 2);      -- 'b'
SELECT string_to_array('a,b,c', ',');    -- {a,b,c}
SELECT array_to_string(ARRAY[1,2,3], '-'); -- '1-2-3'

-- 正则
SELECT regexp_replace('Hello World', '[A-Z]', '*', 'g');  -- '*ello *orld'
SELECT regexp_matches('abc123', '([a-z]+)([0-9]+)');      -- {abc,123}
SELECT regexp_split_to_array('a,b,,c', ',');              -- {a,b,"",c}

-- 取整
SELECT ceil(3.2);     -- 4（向上取整）
SELECT floor(3.8);    -- 3（向下取整）
SELECT round(3.5);    -- 4（四舍五入）
SELECT round(3.567, 2);  -- 3.57
SELECT trunc(3.9);    -- 3（截断小数）
SELECT trunc(3.567, 1);  -- 3.5

-- 数学
SELECT abs(-5);       -- 5（绝对值）
SELECT power(2, 10);  -- 1024（幂）
SELECT sqrt(16);      -- 4（平方根）
SELECT cbrt(27);      -- 3（立方根）
SELECT exp(1);        -- 2.7182818284590452（e 的幂）
SELECT ln(10);        -- 自然对数
SELECT log(100);      -- 常用对数（底为 10）
SELECT log(2, 8);     -- 3（指定底）

-- 三角函数（弧度）
SELECT sin(0), cos(0), tan(0);
SELECT asin(1), acos(0), atan(1);
SELECT radians(180);  -- 3.141592653589793（角度转弧度）
SELECT degrees(3.141592653589793);  -- 180（弧度转角度）

-- 随机
SELECT random();      -- 0 到 1 之间随机数
SELECT setseed(0.5);  -- 设置随机种子

-- 取最大最小
SELECT greatest(1, 2, 3);  -- 3
SELECT least(1, 2, 3);     -- 1

-- 当前时间
SELECT now();                          -- 当前时间戳（带时区）
SELECT current_timestamp;              -- 同 now()
SELECT current_date;                   -- 当前日期
SELECT current_time;                   -- 当前时间
SELECT clock_timestamp();              -- 实时时间戳（每次调用都不同）
SELECT statement_timestamp();          -- 语句开始时间
SELECT transaction_timestamp();        -- 事务开始时间（同 now()）

-- 提取
SELECT extract(year FROM now());       -- 提取年份
SELECT extract(month FROM now());
SELECT extract(dow FROM now());        -- day of week（0=周日）
SELECT date_part('year', now());       -- 同 extract
SELECT date_trunc('month', now());     -- 截断到月初

-- 加减
SELECT now() + INTERVAL '1 day';
SELECT now() - INTERVAL '2 hours';
SELECT now() + INTERVAL '1 year 3 months';

-- 计算
SELECT age('2024-01-01', '2020-01-01');  -- 4 years
SELECT age(timestamp '2000-01-01');      -- 距今多久

-- 格式化
SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS');
SELECT to_char(now(), 'YYYY"年"MM"月"DD"日"');
SELECT to_date('2024-01-15', 'YYYY-MM-DD');
SELECT to_timestamp('2024-01-15 10:30:00', 'YYYY-MM-DD HH24:MI:SS');

-- CAST 标准语法
SELECT CAST('123' AS integer);
SELECT CAST(now() AS date);

-- :: 简写语法（PG 特色）
SELECT '123'::integer;
SELECT '2024-01-01'::date;
SELECT 3.14::numeric(10, 2);

-- CONVERT 风格（不推荐，仅用于编码转换）
SELECT convert('hello', 'UTF8', 'LATIN1');

-- COALESCE：返回第一个非 NULL 值
SELECT COALESCE(email, phone, 'no contact') FROM employees;

-- NULLIF：两值相等返回 NULL，否则返回第一个
SELECT NULLIF(score, 0);  -- score 为 0 时返回 NULL，避免除零

-- GREATEST / LEAST 忽略 NULL
SELECT GREATEST(1, NULL, 3);  -- 3

-- ISNULL / NOTNULL（PG 扩展）
SELECT * FROM employees WHERE email ISNULL;

-- 提取
SELECT data->'name' FROM t;       -- jsonb
SELECT data->>'name' FROM t;      -- text
SELECT data#>'{a,b}' FROM t;      -- 路径提取 jsonb
SELECT data#>>'{a,b}' FROM t;     -- 路径提取 text

-- 包含
SELECT data @> '{"name": "alice"}' FROM t;
SELECT data ? 'name' FROM t;      -- 是否存在键
SELECT data ?| ARRAY['a','b'];    -- 任一键存在
SELECT data ?& ARRAY['a','b'];    -- 所有键存在

-- 修改
SELECT jsonb_set(data, '{name}', '"bob"');
SELECT data || '{"age": 30}'::jsonb;     -- 合并
SELECT data - 'name';                    -- 删除键

-- 转换
SELECT jsonb_array_elements(data->'tags');  -- 展开数组
SELECT jsonb_each(data);                    -- 展开对象为 (key, value)
SELECT jsonb_object_keys(data);             -- 获取所有键

-- 构造
SELECT ARRAY[1, 2, 3];
SELECT array_agg(name) FROM employees;

-- 访问
SELECT arr[1];              -- 第一个元素（PG 数组下标从 1 开始）
SELECT array_length(arr, 1); -- 数组长度

-- 查找
SELECT 2 = ANY(arr);        -- 是否包含
SELECT array_position(arr, 2);  -- 元素位置

-- 操作
SELECT array_append(arr, 4);
SELECT array_prepend(0, arr);
SELECT array_cat(arr1, arr2);   -- 拼接
SELECT array_remove(arr, 2);    -- 删除所有等于 2 的元素
SELECT unnest(arr);             -- 展开为多行

-- 主表：用户基本信息
CREATE TABLE users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username text NOT NULL UNIQUE,
    email text NOT NULL UNIQUE,
    created_at timestamptz DEFAULT now()
);

-- 从表：用户详情（一对一）
CREATE TABLE user_profiles (
    user_id bigint PRIMARY KEY,   -- 主键即外键，天然唯一
    full_name text,
    avatar_url text,
    bio text,
    birthday date,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- INNER JOIN：仅返回有详情的用户
SELECT u.id, u.username, u.email, p.full_name, p.avatar_url
FROM users u
INNER JOIN user_profiles p ON u.id = p.user_id;

-- LEFT JOIN：返回所有用户，无详情的为 NULL
SELECT u.id, u.username, p.full_name
FROM users u
LEFT JOIN user_profiles p ON u.id = p.user_id;

-- 一的一方：部门
CREATE TABLE departments (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL UNIQUE,
    location text,
    created_at timestamptz DEFAULT now()
);

-- 多的一方：员工
CREATE TABLE employees (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    salary numeric(10, 2) CHECK (salary > 0),
    dept_id bigint,   -- 外键列
    created_at timestamptz DEFAULT now(),
    FOREIGN KEY (dept_id) REFERENCES departments(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

-- 查询每个员工及其部门
SELECT e.name, e.salary, d.name AS dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- 查询每个部门的员工数量
SELECT d.name AS dept, COUNT(e.id) AS emp_count
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
GROUP BY d.id, d.name
ORDER BY emp_count DESC;

-- 查询员工数大于 5 的部门
SELECT d.name, COUNT(e.id) AS cnt
FROM departments d
JOIN employees e ON e.dept_id = d.id
GROUP BY d.id, d.name
HAVING COUNT(e.id) > 5;

-- CROSS JOIN 示例：生成所有组合
SELECT d.name AS dept, p.name AS project
FROM departments d
CROSS JOIN projects p;

-- 学生表
CREATE TABLE students (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    student_no text UNIQUE NOT NULL
);

-- 课程表
CREATE TABLE courses (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    credit numeric(3, 1) CHECK (credit > 0)
);

-- 中间表：选课记录
CREATE TABLE enrollments (
    student_id bigint NOT NULL,
    course_id bigint NOT NULL,
    enrolled_at timestamptz DEFAULT now(),
    score numeric(5, 2),
    PRIMARY KEY (student_id, course_id),   -- 复合主键防止重复选课
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);

-- 为反向查询创建索引
CREATE INDEX idx_enrollments_course ON enrollments(course_id);

-- 查询每个学生选了哪些课
SELECT s.name AS student, c.name AS course, e.score
FROM students s
JOIN enrollments e ON e.student_id = s.id
JOIN courses c ON e.course_id = c.id
ORDER BY s.name, c.name;

-- 查询每门课的选课人数
SELECT c.name AS course, COUNT(e.student_id) AS student_count
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.id
GROUP BY c.id, c.name
ORDER BY student_count DESC;

-- 查询选了"数据库原理"课程的学生
SELECT s.name
FROM students s
JOIN enrollments e ON e.student_id = s.id
JOIN courses c ON e.course_id = c.id
WHERE c.name = '数据库原理';

-- 查询同时选了课程 1 和课程 2 的学生（交集）
SELECT s.name
FROM students s
WHERE s.id IN (SELECT student_id FROM enrollments WHERE course_id = 1)
  AND s.id IN (SELECT student_id FROM enrollments WHERE course_id = 2);

-- 员工表（含经理字段）
CREATE TABLE employees (
    id bigint PRIMARY KEY,
    name text NOT NULL,
    manager_id bigint REFERENCES employees(id)
);

-- 查询每个员工及其经理
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

CREATE INDEX idx_employees_salary ON employees(salary);
CREATE INDEX idx_employees_dept_salary ON employees(dept_id, salary);

CREATE INDEX idx_users_email_hash ON users USING hash (email);

-- 范围类型索引
CREATE INDEX idx_bookings_during ON bookings USING gist (during);

-- 几何类型索引
CREATE INDEX idx_cities_location ON cities USING gist (location);

-- JSONB 索引
CREATE INDEX idx_events_data ON events USING gin (data);

-- 数组索引
CREATE INDEX idx_posts_tags ON posts USING gin (tags);

-- 全文搜索索引
CREATE INDEX idx_docs_content ON docs USING gin (to_tsvector('english', content));

-- 时间序列数据
CREATE INDEX idx_logs_created ON logs USING brin (created_at);

CREATE INDEX idx_routes_ip ON routes USING spgist (ip_prefix);

-- 基本创建
CREATE INDEX idx_name ON table(column);

-- 复合索引
CREATE INDEX idx_dept_salary ON employees(dept_id, salary);

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

-- 部分索引（仅索引满足条件的行）
CREATE INDEX idx_active_users ON users(last_login)
WHERE active = true;

-- 表达式索引（索引函数结果）
CREATE INDEX idx_lower_email ON users(lower(email));

-- 并发创建（不阻塞写入，但耗时更长）
CREATE INDEX CONCURRENTLY idx_name ON table(column);

-- 指定索引类型
CREATE INDEX idx_tags ON posts USING gin (tags);

-- 查看索引
\di
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'employees';

-- 查看索引大小
SELECT pg_size_pretty(pg_relation_size('idx_employees_salary'));

-- 查看索引使用情况
SELECT * FROM pg_stat_user_indexes;

-- 重建索引
REINDEX INDEX idx_employees_salary;
REINDEX TABLE employees;          -- 重建表上所有索引
REINDEX TABLE CONCURRENTLY employees;  -- 并发重建（PG 12+）

-- 删除索引
DROP INDEX idx_employees_salary;
DROP INDEX CONCURRENTLY idx_employees_salary;  -- 不阻塞写入

-- 显示执行计划
EXPLAIN SELECT * FROM employees WHERE salary > 8000;

-- 实际执行并显示统计
EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 8000;

-- 显示更多详细信息
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM employees WHERE salary > 8000;

-- 开始事务
BEGIN;
-- 或
BEGIN TRANSACTION;
-- 或
START TRANSACTION;

-- 提交事务
COMMIT;
-- 或
COMMIT TRANSACTION;

-- 回滚事务
ROLLBACK;
-- 或
ROLLBACK TRANSACTION;

BEGIN;
INSERT INTO accounts (id, balance) VALUES (1, 100);
SAVEPOINT sp1;
INSERT INTO accounts (id, balance) VALUES (2, 200);
-- 发现错误，回滚到 sp1
ROLLBACK TO sp1;
-- 继续其他操作
INSERT INTO accounts (id, balance) VALUES (3, 300);
COMMIT;
-- 最终结果：账户 1 和 3 存在，账户 2 不存在

-- 设置当前会话隔离级别
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 设置事务隔离级别
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM accounts WHERE id = 1;
COMMIT;

-- 设置会话默认隔离级别
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 查看表的死元组
SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'employees';

-- 手动 VACUUM（不锁表）
VACUUM employees;

-- VACUUM FULL（重建表，锁表，回收空间）
VACUUM FULL employees;

-- ANALYZE 更新统计信息
ANALYZE employees;

-- VACUUM + ANALYZE
VACUUM ANALYZE employees;

-- 查看当前事务 ID
SELECT txid_current();

-- 查看当前快照
SELECT txid_current_snapshot();

-- 显式加锁
LOCK TABLE employees IN SHARE MODE;
LOCK TABLE employees IN EXCLUSIVE MODE;

-- 8 级锁模式
LOCK TABLE employees IN ACCESS SHARE MODE;
LOCK TABLE employees IN ROW SHARE MODE;
LOCK TABLE employees IN ROW EXCLUSIVE MODE;
LOCK TABLE employees IN SHARE UPDATE EXCLUSIVE MODE;
LOCK TABLE employees IN SHARE MODE;
LOCK TABLE employees IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE employees IN EXCLUSIVE MODE;
LOCK TABLE employees IN ACCESS EXCLUSIVE MODE;

-- 行级锁示例
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 此时其他事务无法修改 id=1 的行
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- NOWAIT 选项：锁不可用时立即报错而非等待
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

-- SKIP LOCKED 选项：跳过已锁定的行（适合任务队列）
SELECT * FROM task_queue WHERE status = 'pending'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 10;

-- 死锁示例
-- 事务 A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 此时事务 B 执行: UPDATE accounts SET balance = balance - 100 WHERE id = 2;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- 等待事务 B
-- 事务 B 执行: UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- 死锁！PG 自动检测并终止其中一个事务

-- 会话级咨询锁（直到显式释放或会话结束）
pg_advisory_lock(12345);          -- 整数键
pg_advisory_lock(1, 1);           -- 双整数键
pg_advisory_unlock(12345);        -- 释放

-- 事务级咨询锁（事务结束时自动释放）
pg_advisory_xact_lock(12345);

-- 尝试获取，不阻塞
pg_try_advisory_lock(12345);      -- 返回 boolean

-- 共享咨询锁（多个会话可同时持有）
pg_advisory_lock_shared(12345);

-- 查看当前锁情况
SELECT * FROM pg_locks;

-- 查看阻塞关系
SELECT
    blocked.pid     AS blocked_pid,
    blocked.query   AS blocked_query,
    blocking.pid    AS blocking_pid,
    blocking.query  AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks ul ON ul.locktype = bl.locktype
    AND ul.database IS NOT DISTINCT FROM bl.database
    AND ul.relation IS NOT DISTINCT FROM bl.relation
    AND ul.granted
JOIN pg_stat_activity blocking ON ul.pid = blocking.pid;

-- 终止会话
SELECT pg_terminate_backend(pid);
SELECT pg_cancel_backend(pid);  -- 仅取消当前查询

-- 基本 CTE
WITH dept_stats AS (
    SELECT dept_id, COUNT(*) AS emp_count, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY dept_id
)
SELECT d.name, ds.emp_count, ds.avg_sal
FROM departments d
JOIN dept_stats ds ON ds.dept_id = d.id
WHERE ds.avg_sal > 8000;

-- 多个 CTE
WITH
active_users AS (
    SELECT * FROM users WHERE active = true
),
recent_orders AS (
    SELECT * FROM orders WHERE created_at > now() - INTERVAL '30 days'
)
SELECT au.username, COUNT(ro.id) AS order_count
FROM active_users au
LEFT JOIN recent_orders ro ON ro.user_id = au.id
GROUP BY au.id, au.username;

-- 员工层级（自引用）
WITH RECURSIVE emp_tree AS (
    -- 锚点：顶层（无经理）
    SELECT id, name, manager_id, 1 AS level, name::text AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 递归：下属
    SELECT e.id, e.name, e.manager_id, et.level + 1, et.path || ' > ' || e.name
    FROM employees e
    JOIN emp_tree et ON e.manager_id = et.id
)
SELECT id, name, level, path
FROM emp_tree
ORDER BY path;

-- 生成 1 到 10 的序列
WITH RECURSIVE nums(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 10
)
SELECT * FROM nums;

-- 斐波那契数列
WITH RECURS fib(a, b) AS (
    SELECT 0, 1
    UNION ALL
    SELECT b, a + b FROM fib WHERE b < 100
)
SELECT a FROM fib;

-- 强制物化（适合多次引用且计算昂贵的 CTE）
WITH expensive AS MATERIALIZED (
    SELECT * FROM big_table WHERE complex_condition
)
SELECT * FROM expensive e1
JOIN expensive e2 ON e1.id = e2.parent_id;

-- 强制内联（适合简单 CTE，让优化器下推条件）
WITH simple AS NOT MATERIALIZED (
    SELECT * FROM users
)
SELECT * FROM simple WHERE name = 'alice';

-- 创建视图
CREATE VIEW v_high_salary_employees AS
SELECT id, name, salary, dept_id
FROM employees
WHERE salary > 10000;

-- 使用视图
SELECT * FROM v_high_salary_employees;
SELECT * FROM v_high_salary_employees WHERE dept_id = 1;

-- 修改视图
CREATE OR REPLACE VIEW v_high_salary_employees AS
SELECT id, name, salary, dept_id, email
FROM employees
WHERE salary > 10000;

-- 重命名视图
ALTER VIEW v_high_salary_employees RENAME TO v_top_earners;

-- 删除视图
DROP VIEW v_high_salary_employees;
DROP VIEW IF EXISTS v_high_salary_employees CASCADE;

-- 可更新视图
CREATE VIEW v_active_users AS
SELECT id, username, email FROM users WHERE active = true;

-- 直接插入（active 自动设为 true）
INSERT INTO v_active_users (id, username, email) VALUES (1, 'alice', 'a@b.com');

-- 直接更新
UPDATE v_active_users SET email = 'new@b.com' WHERE id = 1;

-- 直接删除
DELETE FROM v_active_users WHERE id = 1;

-- 创建物化视图
CREATE MATERIALIZED VIEW mv_dept_stats AS
SELECT dept_id, COUNT(*) AS emp_count, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id;

-- 创建时带数据
CREATE MATERIALIZED VIEW mv_dept_stats AS
SELECT dept_id, COUNT(*) AS emp_count, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id
WITH DATA;  -- 默认，WITH NO DATA 则不立即填充

-- 刷新（默认锁表）
REFRESH MATERIALIZED VIEW mv_dept_stats;

-- 并发刷新（不锁表，需要唯一索引）
CREATE UNIQUE INDEX idx_mv_dept_stats_dept ON mv_dept_stats(dept_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dept_stats;

-- 删除
DROP MATERIALIZED VIEW mv_dept_stats;

-- 创建可登录角色（用户）
CREATE ROLE alice LOGIN PASSWORD 'secure_password';
CREATE USER bob PASSWORD 'secure_password';  -- CREATE USER 默认带 LOGIN

-- 创建不可登录角色（组）
CREATE ROLE dev_team;

-- 创建带属性的角色
CREATE ROLE admin SUPERUSER CREATEDB CREATEROLE REPLICATION LOGIN PASSWORD 'admin_pass';

-- 完整属性说明
CREATE ROLE role_name WITH
    LOGIN                 -- 允许登录
    PASSWORD 'pass'       -- 密码
    SUPERUSER             -- 超级用户（慎用）
    CREATEDB              -- 可创建数据库
    CREATEROLE            -- 可创建角色
    REPLICATION           -- 可做流复制
    CONNECTION LIMIT 100  -- 最大连接数
    VALID UNTIL '2025-12-31'  -- 密码有效期
    IN ROLE dev_team;     -- 加入 dev_team 组

-- 修改密码
ALTER ROLE alice PASSWORD 'new_password';

-- 修改属性
ALTER ROLE alice CREATEDB;
ALTER ROLE alice NOLOGIN;  -- 禁止登录

-- 重命名
ALTER ROLE alice RENAME TO alice_new;

-- 设置角色参数
ALTER ROLE alice SET work_mem = '64MB';
ALTER ROLE alice SET search_path TO myapp, public;

-- 删除角色
DROP ROLE alice;
-- 如果角色有依赖对象，需先转移或删除依赖
DROP ROLE IF EXISTS alice;

-- 授予表权限
GRANT SELECT, INSERT, UPDATE ON employees TO alice;
GRANT ALL PRIVILEGES ON employees TO admin;

-- 授予列级权限
GRANT SELECT (id, name), UPDATE (salary) ON employees TO alice;

-- 授予 schema 权限
GRANT USAGE, CREATE ON SCHEMA myapp TO alice;

-- 授予数据库权限
GRANT CONNECT, TEMPORARY ON DATABASE mydb TO alice;

-- 授予序列权限
GRANT USAGE, SELECT, UPDATE ON SEQUENCE users_id_seq TO alice;

-- 授予函数权限
GRANT EXECUTE ON FUNCTION my_func(integer) TO alice;

-- 授予所有表（schema 内）
GRANT SELECT ON ALL TABLES IN SCHEMA myapp TO alice;

-- 授予所有序列
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA myapp TO alice;

-- 撤销权限
REVOKE INSERT, UPDATE ON employees FROM alice;
REVOKE ALL PRIVILEGES ON employees FROM admin;

-- 设置默认权限：alice 在 myapp schema 下新建的表，dev_team 自动有 SELECT 权限
ALTER DEFAULT PRIVILEGES FOR ROLE alice IN SCHEMA myapp
GRANT SELECT ON TABLES TO dev_team;

-- 默认序列权限
ALTER DEFAULT PRIVILEGES FOR ROLE alice IN SCHEMA myapp
GRANT USAGE ON SEQUENCES TO dev_team;

-- 默认函数权限
ALTER DEFAULT PRIVILEGES FOR ROLE alice IN SCHEMA myapp
GRANT EXECUTE ON FUNCTIONS TO dev_team;

-- 创建组角色
CREATE ROLE dev_team;
CREATE ROLE dev_lead;

-- 将 alice 加入 dev_team
GRANT dev_team TO alice;

-- 将 dev_team 加入 dev_lead（层级）
GRANT dev_team TO dev_lead;

-- 设置 dev_team 权限
GRANT SELECT ON ALL TABLES IN SCHEMA myapp TO dev_team;

-- alice 通过继承获得 dev_team 的权限
-- 默认角色带 INHERIT 属性，自动继承成员角色权限
CREATE ROLE alice LOGIN INHERIT;  -- INHERIT 是默认

-- 切换到成员角色（SET ROLE）
SET ROLE dev_team;  -- 当前会话以 dev_team 身份操作
RESET ROLE;         -- 恢复原始角色