在PostgreSQL中物化视图没有主键时如何在SQL中高效去重？-SQL-PHP中文网

物化视图不支持主键，但查询时可安全使用 distinct；需避免在创建时加 order by 或尝试 update；多列去重按组合判重，null 视为相同；留最新/最早行须用 row_number() 窗口函数；索引可加速 distinct 查询；refresh 非增量且成本高。

在postgresql中物化视图没有主键时如何在sql中高效去重？

物化视图本身不支持主键，但 DISTINCT 仍可安全使用

PostgreSQL 的物化视图（MATERIALIZED VIEW）不支持定义主键或唯一约束，这是设计使然——它本质是快照，不是真实表。但这不影响你在查询它时用 DISTINCT 去重，只要语法合规即可。关键点在于：DISTINCT 是查询时的行为，和底层是否可写、是否有索引无关。

常见错误是试图在创建物化视图时加 ORDER BY 或对它直接 UPDATE：CREATE MATERIALIZED VIEW v AS SELECT DISTINCT x FROM t ORDER BY x; 在 PostgreSQL 中会报错；而后续执行 UPDATE v SET ... 则必然失败，因为物化视图不可更新。

DISTINCT 必须放在 SELECT 后紧邻位置，不能写成 SELECT x, DISTINCT y
多列去重时，DISTINCT a, b 表示按 (a,b) 组合判重，NULL 会被视为相同值参与比较
如果物化视图基表很大，且你只查某几列，显式写出列名比 SELECT DISTINCT * 快得多——避免全字段序列化与哈希开销

当需要“保留最新一条”而非简单去重时，必须绕过 DISTINCT

DISTINCT 只能保证行唯一，无法控制哪一行被留下。比如物化视图里存了用户操作日志，同一 user_id 多次出现，你想留 created_at 最大的那条——这时 DISTINCT 完全无能为力。

正确做法是用窗口函数，在查询物化视图时动态标序：

SELECT user_id, email, created_at
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY user_id 
           ORDER BY created_at DESC
         ) AS rn
  FROM my_mv
) t
WHERE rn = 1;

注意 PARTITION BY 字段必须覆盖你定义的“重复逻辑”，漏掉 email 就可能把同名不同邮箱的用户合并了
ORDER BY created_at DESC 决定了谁是 rn = 1；若想留最早一条，改成 ASC
该写法在 PostgreSQL 9.4+ 全支持，无需额外权限，但性能依赖 my_mv 上是否对 (user_id, created_at) 有索引

物化视图 + 索引才能让 DISTINCT 查询真正变快

物化视图本身不加速 DISTINCT，除非你手动给它建索引。PostgreSQL 允许对物化视图建普通 B-tree 索引，这对去重查询有实质帮助：

例如物化视图 mv_users 包含 country 和 city，你常查 SELECT DISTINCT country FROM mv_users，那就应该建：

CREATE INDEX idx_mv_users_country ON mv_users (country);

索引能让 DISTINCT 避开全扫描，转为索引遍历 + 去重，尤其在高基数列（如国家名）上效果明显
多列组合去重（如 DISTINCT country, city）建议建联合索引：CREATE INDEX idx_mv_users_cc ON mv_users (country, city)
不要指望物化视图自动维护索引——每次 REFRESH MATERIALIZED VIEW 后，索引仍有效，但若数据量剧增，可能需要 VACUUM 或重建索引

别把物化视图当临时表用：REFRESH 成本远高于想象

有人想“先去重再固化”，于是建一个 SELECT DISTINCT * 的物化视图，以为一劳永逸。但问题在于：REFRESH MATERIALIZED VIEW CONCURRENTLY 要求唯一索引，而你又没法在物化视图上建唯一索引（因为没主键），结果只能用阻塞式 REFRESH，期间整个视图不可读。

更现实的路径是：把去重逻辑留在查询层，物化视图只做轻量聚合或宽表预计算。比如：