如何对比两个查询结果？PostgreSQL中EXCEPT语句为何失效？

首先，PostgreSQL的普通SQL不需要单独声明变量，直接写条件即可；如果想复用查询逻辑，用CTE（WITH子句）会更方便。

对比两个predicate的差异 #

要找出predicate='description'和predicate='#shortDescription'的所有差异（两边各自独有的记录），得用EXCEPT ALL配合UNION ALL——普通EXCEPT会自动去重，要是你想看所有重复的差异记录，必须用EXCEPT ALL：

-- 找出仅在description里有的记录
SELECT entity_id, value
FROM entity_attributes
WHERE predicate = 'description'
EXCEPT ALL
SELECT entity_id, value
FROM entity_attributes
WHERE predicate = '#shortDescription'

UNION ALL

-- 找出仅在#shortDescription里有的记录
SELECT entity_id, value
FROM entity_attributes
WHERE predicate = '#shortDescription'
EXCEPT ALL
SELECT entity_id, value
FROM entity_attributes
WHERE predicate = 'description';

检查重复记录 #

要是你想检查每个predicate自身有没有重复的记录，用分组查询就行：

-- 查description的重复
SELECT entity_id, value, COUNT(*) AS duplicate_count
FROM entity_attributes
WHERE predicate = 'description'
GROUP BY entity_id, value
HAVING COUNT(*) > 1;

-- 查#shortDescription的重复
SELECT entity_id, value, COUNT(*) AS duplicate_count
FROM entity_attributes
WHERE predicate = '#shortDescription'
GROUP BY entity_id, value
HAVING COUNT(*) > 1;

你的EXCEPT不生效的可能原因 #

1. 字段不匹配：EXCEPT要求两个查询的字段数量、数据类型完全一致，要是两边查的字段不一样，结果肯定不符合预期；
2. 自动去重的坑：普通EXCEPT会把重复的差异记录过滤掉，用EXCEPT ALL才能保留所有重复的差异；
3. 条件写错：比如predicate的字符串大小写错误（PostgreSQL默认区分大小写）、拼写错误（比如少写了#符号）。

结合你表结构的完整示例 #

用CTE把两个查询封装起来，可读性更好：

WITH desc_data AS (
    SELECT entity_id, value
    FROM entity_attributes
    WHERE predicate = 'description'
),
short_desc_data AS (
    SELECT entity_id, value
    FROM entity_attributes
    WHERE predicate = '#shortDescription'
)
-- 显示差异类型+对应记录
SELECT '仅在description中存在' AS diff_type, entity_id, value FROM desc_data
EXCEPT ALL
SELECT '仅在description中存在' AS diff_type, entity_id, value FROM short_desc_data

UNION ALL

SELECT '仅在#shortDescription中存在' AS diff_type, entity_id, value FROM short_desc_data
EXCEPT ALL
SELECT '仅在#shortDescription中存在' AS diff_type, entity_id, value FROM desc_data;

内容的提问来源于stack exchange，提问作者Camel4488