在PostgreSQL 16中如何利用DISTINCT ON实现特定字段的唯一性

DISTINCT ON 不是让字段唯一，而是按指定字段分组后取每组 ORDER BY 排序的第一整行；其 ORDER BY 必须严格以前缀匹配方式包含 DISTINCT ON 字段，否则报错；结果稳定性与性能高度依赖 ORDER BY 严谨性和联合索引支持。

DISTINCT ON 不是“让字段唯一”，而是按字段分组后取每组第一行——结果是否符合业务预期，全看 ORDER BY 写得够不够严。

为什么加了 DISTINCT ON 还报错？

PostgreSQL 会直接拒绝执行，只要 ORDER BY 不满足前缀匹配规则。这不是警告，是硬性语法约束：

• DISTINCT ON (user_id, status) 要求 ORDER BY 必须以 user_id, status 开头（顺序、数量、表达式完全一致）
• ORDER BY user_id, created_at DESC → 报错：缺少 status
• ORDER BY status, user_id → 报错：顺序颠倒
• ORDER BY user_id, status, created_at DESC → 合法，且推荐：多加排序字段可避免时间相同时结果随机

DISTINCT ON 和普通 DISTINCT 的行为差异很关键

很多人以为 DISTINCT ON (a) 是“按 a 去重”，其实它保留的是每组中 ORDER BY 排序后的第一整行，而 DISTINCT a 只返回 a 列本身：

• SELECT DISTINCT name FROM company → 输出只有 name 列，7 行
• SELECT DISTINCT ON (name) name, age, salary FROM company → 输出三列，仍是 7 行，但每 name 对应哪条 age/salary，由 ORDER BY name, salary DESC 决定
• 前者等价于 GROUP BY name 投影；后者等价于窗口函数 ROW_NUMBER() OVER (PARTITION BY name ORDER BY salary DESC) 取 rn = 1

没有索引时 DISTINCT ON 可能比 ROW_NUMBER() 还慢

别迷信语法糖。当数据量大、没建对索引时，DISTINCT ON 会触发大量排序和临时磁盘写入：

• 高频查询如 SELECT DISTINCT ON (user_id) * FROM events ORDER BY user_id, created_at DESC，必须配联合索引：CREATE INDEX idx_events_user_created ON events (user_id, created_at DESC)
• 索引列顺序必须和 DISTINCT ON + ORDER BY 前缀完全一致，否则优化器大概率不用
• 实测 1000 万行数据下，有合适索引时 DISTINCT ON 比 ROW_NUMBER() 快 20–80%；无索引时反而更慢

最容易被忽略的，是把 DISTINCT ON 当成“自动智能去重”。它不猜你要最新还是最早，也不保证稳定性——你写的 ORDER BY 缺一个字段、少一个方向、没建索引，结果就可能下次执行就变。