PostgreSQL 常用命令与函数全能速查表 (PostgreSQL CheatSheet)

-- 创建模式并指定拥有者
CREATE SCHEMA IF NOT EXISTS core_app AUTHORIZATION postgres;

-- 设置当前会话的搜索路径 (优先访问哪个 Schema)
SET search_path TO core_app, public;

-- 创建带有 UUID 主键与 JSONB 字段的现代业务表
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    username VARCHAR(50) NOT NULL UNIQUE,
    profile JSONB NOT NULL DEFAULT '{}',
    tags TEXT[] DEFAULT '{}',
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 1. 正则匹配与提取
SELECT '2026-10-30' ~ '^\d{4}-\d{2}-\d{2}$';  -- 返回 true
SELECT SUBSTRING('order-2026-v1' FROM 'order-(\d{4})-'); -- 提取 '2026'

-- 2. 字符串聚合 (STRING_AGG)
SELECT dept_id, STRING_AGG(emp_name, ', ' ORDER BY salary DESC)
FROM employees
GROUP BY dept_id;

-- 3. 补齐与格式化
SELECT LPAD('123', 6, '0'); -- 输出 '000123'

-- 提取指定日期字段 (EXTRACT / DATE_PART)
SELECT EXTRACT(YEAR FROM NOW()), EXTRACT(DOW FROM NOW()); -- DOW: 星期几 (0-6)

-- 时间间隔运算 (INTERVAL)
SELECT NOW() + INTERVAL '3 days 2 hours';
SELECT NOW() - INTERVAL '1 month';

-- 生成连续时间序列 (生成模拟数据神器)
SELECT generate_series(
    '2026-10-01'::date, 
    '2026-10-07'::date, 
    '1 day'::interval
)::date AS day;

-- 1. 标准 B-Tree 索引 (通用查找、等值、范围查询)
CREATE INDEX idx_users_created_at ON users (created_at DESC);

-- 2. GIN 倒排索引 (针对 JSONB、数组、全文检索极速加速)
CREATE INDEX idx_users_profile_gin ON users USING GIN (profile);
-- 这样 profile @> '{"role": "admin"}' 能够走毫秒级索引扫描！

-- 3. 部分条件索引 (Partial Index: 仅为符合条件的行建立索引，节省空间)
CREATE INDEX idx_unpaid_orders ON orders (customer_id) WHERE status = 'unpaid';

-- 4. 表达式/函数索引
CREATE INDEX idx_lower_email ON users (LOWER(email));

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM users WHERE profile @> '{"vip": true}';

SELECT 
    pid, 
    usename, 
    client_addr, 
    state, 
    NOW() - query_start AS duration, 
    query 
FROM pg_stat_activity
WHERE state != 'idle' 
ORDER BY duration DESC;

SELECT 
    blocked_locks.pid     AS blocked_pid,
    blocked_activity.usename  AS blocked_user,
    blocking_locks.pid    AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query    AS blocked_statement,
    blocking_activity.query   AS blocking_statement
FROM  pg_catalog.pg_locks         blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks         blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- 方式 A: 优雅取消正在运行的查询 (不切断连接)
SELECT pg_cancel_backend(12345);

-- 方式 B: 强制杀掉会话 (相当于 kill -9)
SELECT pg_terminate_backend(12345);