⚡

PostgreSQL查询专家Skill postgres-query-expert

这个技能是PostgreSQL 16数据库查询和管理的综合指南，涵盖SQL查询构造、性能优化、模式管理、系统内省等，适用于数据库开发和管理，关键词包括PostgreSQL、SQL查询、数据库优化、后端开发、数据管理。

后端开发 0 次安装 0 次浏览 更新于 3/9/2026

名称：postgres-query-expert 描述：一个用于与PostgreSQL 16数据库交互的综合指南。使用此技能构建标准和高级SQL查询、优化性能、调试错误、管理模式对象和内省数据库结构。 允许工具：读取、grep、glob

PostgreSQL 查询专家

此技能是PostgreSQL 16的权威参考，涵盖查询构造、优化、模式管理和系统内省。

指令

1. 通用查询标准

  • 语法：遵循ANSI SQL标准，但当PostgreSQL扩展（例如DISTINCT ON、RETURNING、LATERAL、FILTER子句）提供更清晰的逻辑或更好的性能时，优先使用它们。
  • 标识符：对所有标识符使用snake_case。仅在绝对必要时引用标识符（如"MyTable"）；优先使用小写未引用的名称。
  • 安全性：
    • 参数化：始终对字面值使用参数（$1、$2、…）。切勿直接注入用户输入。
    • 超时设置：对于大型数据库的探索性查询，前置SET LOCAL statement_timeout = '30s';。
    • 事务：对多步骤操作使用显式BEGIN和COMMIT块。

2. 性能与优化

  • 解释计划：使用EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)诊断瓶颈。
  • 红色标志：大型表上的Seq Scan、高Buffers: shared hit（内存使用）或Disk: read（I/O）。
  • 索引：根据使用情况推荐特定索引类型：
    • B树：用于标准相等/范围查询（=、<、>）。
    • GIN：用于复合类型如JSONB（@>）或数组（&&），以及全文搜索。
    • GiST：用于几何数据和范围。
  • CTE：使用公共表表达式（WITH）以提高可读性。在PG16+中，这些默认优化（内联），除非指定MATERIALIZED。

内省（代理能力）

探索新数据库时，使用这些查询来理解模式。

列出所有表

SELECT n.nspname AS 模式,
       c.relname AS 表,
       obj_description(c.oid) AS 描述
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;

获取表列和类型

SELECT a.attname AS 列,
       format_type(a.atttypid, a.atttypmod) AS 类型,
       a.attnotnull AS 非空,
       col_description(a.attrelid, a.attnum) AS 注释
FROM pg_attribute a
WHERE a.attrelid = 'public.target_table_name'::regclass
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY a.attnum;

参考：数据查询（DQL）

高级聚合

  • Filter子句：count(*) FILTER (WHERE status = 'active')
  • 分组集：GROUP BY GROUPING SETS ((brand), (brand, category), ())
  • 任意值：any_value(col)（PG16+）返回组中的任意值。

窗口函数

跨与当前行相关的表行集执行计算。

SELECT dept,
       emp_no,
       salary,
       -- 按部门内薪资排名员工
       dense_rank() OVER (PARTITION BY dept ORDER BY salary DESC) AS 排名,
       -- 薪资运行总计
       sum(salary) OVER (
         PARTITION BY dept
         ORDER BY salary
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS 运行总计
FROM employees;

模式匹配

  • LIKE：col LIKE 'foo%'（简单通配符）。
  • ILIKE：col ILIKE 'foo%'（不区分大小写）。
  • SIMILAR TO：col SIMILAR TO '[a-c]%'（SQL正则风格）。
  • POSIX正则：
    • 区分大小写：col ~ '^[a-z]+$'
    • 不区分大小写：col ~* 'foo'

参考：数据修改（DML）

MERGE（更新/插入 / 条件操作）

基于连接条件插入、更新或删除的标准SQL方法（PG15+）。

MERGE INTO wine_stock ws
USING wine_shipments s
  ON s.winery_id = ws.winery_id
 AND s.year = ws.year
WHEN MATCHED THEN
  UPDATE SET stock = ws.stock + s.count
WHEN NOT MATCHED THEN
  INSERT (winery_id, year, stock)
  VALUES (s.winery_id, s.year, s.count);

INSERT … ON CONFLICT（旧版更新/插入）

PostgreSQL特定，对于简单唯一键冲突通常更简洁。

INSERT INTO kv_store (key, value)
VALUES ('config', '{"a":1}')
ON CONFLICT (key)
DO UPDATE SET value = EXCLUDED.value;

RETURNING 子句

立即从修改的行返回数据。

DELETE FROM archived_logs
WHERE created_at < NOW() - INTERVAL '1 year'
RETURNING id, created_at;

参考：特殊数据类型

JSONB（二进制JSON）

在存储和索引时优先使用jsonb而非json。

操作符 描述 示例
-> / ->> 获取元素（JSON / 文本） data->'key'
@> 包含（可索引） data @> '{"tag": "urgent"}'
? 键存在 data ? 'error'
#- 删除路径 data #- '{info, sensitive}'

SQL/JSON路径（PG12+）：

-- 查找所有价格大于10的物品
SELECT jsonb_path_query(data, '$.items[*] ? (@.price > 10)')
FROM orders;

数组

SELECT ARRAY[1,2,3];           -- 创建
SELECT (ARRAY[1,2,3])[1];      -- 访问（基于1的索引）
SELECT 1 = ANY(arr_col);       -- 检查值是否存在于数组中
SELECT unnest(arr_col) FROM t; -- 将数组展开为行

范围类型

用于调度和有效期。

  • tstzrange：带时区的时间戳范围。
  • int4range、daterange：整数和日期范围。
  • 重叠操作符（&&）：检查两个范围是否重叠。
SELECT *
FROM reservations
WHERE duration && tstzrange('2023-01-01 10:00', '2023-01-01 12:00');

参考：系统管理与统计

终止长时间运行的查询

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
  AND pid <> pg_backend_pid()
  AND query_start < NOW() - INTERVAL '5 minutes';

检查表大小（磁盘使用）

SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS 总大小,
       pg_size_pretty(pg_relation_size(relid)) AS 数据大小
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

示例

场景1：递归CTE用于图/树数据

导航组织层次结构。

WITH RECURSIVE subordinates AS (
    -- 基础情况：经理
    SELECT employee_id, manager_id, full_name, 0 AS 层级
    FROM employees
    WHERE employee_id = $1

    UNION ALL

    -- 递归步骤：直接下属
    SELECT e.employee_id, e.manager_id, e.full_name, s.层级 + 1
    FROM employees e
    INNER JOIN subordinates s ON s.employee_id = e.manager_id
)
SELECT *
FROM subordinates;

场景2：Lateral连接用于“每个类别的前N个”

高效获取每个用户的最新3条帖子。

SELECT u.username, p.title, p.created_at
FROM users u
CROSS JOIN LATERAL (
    SELECT title, created_at
    FROM posts
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) p
WHERE u.status = 'active';

场景3：全文搜索与排名

搜索博客表。

SELECT id,
       title,
       ts_rank(to_tsvector('english', title || ' ' || content), query) AS 排名
FROM articles,
     to_tsquery('english', 'postgres | optimization') query
WHERE to_tsvector('english', title || ' ' || content) @@ query
ORDER BY 排名 DESC;