⚡

PostgreSQL查询专家Skill postgres-query-expert

这个技能是关于PostgreSQL 16数据库的全面指南，用于构建标准和高级SQL查询、优化性能、调试错误、管理数据库模式和结构内省。适用于数据库开发人员、数据工程师和数据分析师，提升数据库操作效率和数据处理能力。关键词：PostgreSQL、SQL查询、数据库优化、数据工程、模式管理、性能调优、SQL调试。

数据工程 0 次安装 0 次浏览 更新于 3/9/2026

name: postgres-query-expert description: 一个用于与PostgreSQL 16数据库交互的全面指南。使用此技能构建标准和高級SQL查询、优化性能、调试错误、管理模式对象和内省数据库结构。 allowed-tools: Read, 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（RAM使用）或Disk: read（I/O）。
  • 索引: 基于使用推荐特定索引类型：
    • B-tree: 标准等式/范围（=、<、>）查询。
    • GIN: 用于复合类型如JSONB（@>）或数组（&&），以及全文搜索。
    • GiST: 用于几何数据和范围。
  • CTE: 使用公共表表达式（WITH）提高可读性。在PG16+中，这些默认优化（内联），除非指定MATERIALIZED。

内省（代理能力）

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

列出所有表

SELECT n.nspname AS schema,
       c.relname AS table,
       obj_description(c.oid) AS description
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 column,
       format_type(a.atttypid, a.atttypmod) AS type,
       a.attnotnull AS not_null,
       col_description(a.attrelid, a.attnum) AS comment
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 rank,
       -- 工资运行总计
       sum(salary) OVER (
         PARTITION BY dept
         ORDER BY salary
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
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（Upsert / 条件操作）

基于连接条件的插入、更新或删除的标准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（传统Upsert）

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 total_size,
       pg_size_pretty(pg_relation_size(relid)) AS data_size
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 level
    FROM employees
    WHERE employee_id = $1

    UNION ALL

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

场景2：Lateral Join用于“每个类别前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 rank
FROM articles,
     to_tsquery('english', 'postgres | optimization') query
WHERE to_tsvector('english', title || ' ' || content) @@ query
ORDER BY rank DESC;