进阶特性

PostgreSQL 的强大之处在于其类型系统与查询能力。本章覆盖最常被称道的特性。

JSONB：可索引的半结构化数据

JSONB 以二进制存储，支持索引与丰富的操作符；JSON 则保留原始文本与空格。

操作符	含义
`->'key'`	取 JSON 字段（返回 JSON）
`->>'key'`	取 JSON 字段（返回文本）
`#>'path'`	按路径取值
`@>`	包含（用于 GIN 索引）
`?`	键是否存在

CREATE TABLE products (
    id    BIGSERIAL PRIMARY KEY,
    attrs JSONB NOT NULL DEFAULT '{}'
);

-- GIN 索引支撑 @>、? 等包含查询
CREATE INDEX idx_products_attrs ON products USING GIN (attrs);

INSERT INTO products (attrs) VALUES
  ('{"color":"red","sizes":[38,39,40],"brand":"acme"}');

SELECT attrs->>'color' AS color FROM products WHERE attrs @> '{"brand":"acme"}';

-- jsonb_path_query：用 SQL/JSON 路径查询
SELECT jsonb_path_query(attrs, '$.sizes[*]') FROM products;

Tip

JSONB 适合属性不固定的扩展信息（如商品参数、事件负载）。但它不替代关系建模：频繁作为查询条件的字段应抽成独立列并建普通索引，性能与约束都更好。

数组

CREATE TABLE posts (
    id   BIGSERIAL PRIMARY KEY,
    tags TEXT[] NOT NULL DEFAULT '{}'
);

-- GIN 索引加速数组包含
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

SELECT * FROM posts WHERE tags @> ARRAY['go', 'database'];
SELECT * FROM posts WHERE 'go' = ANY(tags);

-- 聚合为数组
SELECT array_agg(tag) FROM unnest(ARRAY['a','b','c']) AS tag;

CTE 与递归查询

-- 普通 CTE：提升可读性
WITH paid AS (
    SELECT user_id, SUM(amount) AS gmv
    FROM orders WHERE status = 'paid'
    GROUP BY user_id
)
SELECT * FROM paid WHERE gmv > 1000 ORDER BY gmv DESC;

-- 递归 CTE：查询组织架构 / 树形结构
WITH RECURSIVE tree AS (
    SELECT id, name, manager_id, 1 AS depth
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, t.depth + 1
    FROM employees e JOIN tree t ON e.manager_id = t.id
)
SELECT * FROM tree ORDER BY depth, id;

窗口函数

-- 每个城市取销售额前三的商品
SELECT city, product, amount
FROM (
    SELECT city, product, amount,
           ROW_NUMBER() OVER (PARTITION BY city ORDER BY amount DESC) AS rn
    FROM sales
) t
WHERE rn <= 3;

LATERAL：相关连接

LATERAL 允许子查询引用前面表的列，实现「每行取 Top-N」：

SELECT u.name, o.id, o.amount
FROM users u
CROSS JOIN LATERAL (
    SELECT id, amount
    FROM orders
    WHERE user_id = u.id
    ORDER BY amount DESC
    LIMIT 3
) o;

UPSERT

INSERT INTO inventory (sku, qty) VALUES ('A001', 10)
ON CONFLICT (sku)
DO UPDATE SET qty = inventory.qty + EXCLUDED.qty;

-- 冲突时忽略
INSERT INTO inventory (sku, qty) VALUES ('A001', 10)
ON CONFLICT (sku) DO NOTHING;

DISTINCT ON

-- 每个用户取最近一条订单（比窗口函数更简洁）
SELECT DISTINCT ON (user_id) user_id, id, created_at
FROM orders
ORDER BY user_id, created_at DESC;

扩展生态

CREATE EXTENSION IF NOT EXISTS pg_trgm;      -- 模糊匹配 / 相似度
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";  -- UUID 生成

-- pg_trgm：为 LIKE '%...%' 提供索引支撑
CREATE INDEX idx_users_name_trgm ON users USING GIN (name gin_trgm_ops);

其他知名扩展：PostGIS（地理空间）、pgvector（向量检索，用于 RAG）、TimescaleDB（时序）、Citus（分布式）、pg_stat_statements（SQL 统计）。

Warning

使用扩展前确认目标环境已安装对应扩展包（很多托管 PG 限制了可用的扩展白名单）。迁移时也要一并核对扩展清单。

小结

JSONB + GIN 索引是处理半结构化数据的利器，但不要滥用。
数组、CTE、递归、窗口、LATERAL、DISTINCT ON 让复杂查询更简洁。
ON CONFLICT 实现优雅的 UPSERT。
扩展是 PG 的护城河：PostGIS、pgvector、TimescaleDB 覆盖了大量专用场景。

#进阶特性

#JSONB：可索引的半结构化数据

#数组

#CTE 与递归查询

#窗口函数

#LATERAL：相关连接

#UPSERT

#DISTINCT ON

#扩展生态

#小结