如何在PostgreSQL中使用EXCEPT运算符实现大小写不敏感比对？

你遇到的报错是因为Latin1_General_CI_AS是SQL Server专属的大小写不敏感排序规则，PostgreSQL的UTF8编码环境下不支持这个规则，以下是几种适配PostgreSQL的解决方法：

方案1：使用PostgreSQL内置的大小写不敏感排序规则 #

PostgreSQL提供了通用Unicode大小写不敏感排序规则und_ci，直接替换即可实现需求：

SELECT "Address" COLLATE und_ci FROM Table_1
EXCEPT
SELECT "People Address" COLLATE und_ci FROM Table_2;

如果你的数据库安装了对应语言的本地化排序规则（比如en_US.utf8_ci），也可以替换使用，效果一致。

方案2：统一转换为小写/大写后比对 #

如果不想依赖特定排序规则，直接将两列内容统一转换为小写（或大写）后再执行EXCEPT，同样能实现大小写不敏感比对：

SELECT LOWER("Address") FROM Table_1
EXCEPT
SELECT LOWER("People Address") FROM Table_2;

要是需要返回原始的Address值（而非转换后的小写内容），可以改用NOT EXISTS子查询：

SELECT t1."Address"
FROM Table_1 t1
WHERE NOT EXISTS (
    SELECT 1
    FROM Table_2 t2
    WHERE LOWER(t1."Address") = LOWER(t2."People Address")
);

或者用LEFT JOIN的写法：

SELECT t1."Address"
FROM Table_1 t1
LEFT JOIN Table_2 t2 
  ON LOWER(t1."Address") = LOWER(t2."People Address")
WHERE t2."People Address" IS NULL;

方案3：（可选）设置永久的列排序规则 #

如果后续需要频繁进行这类大小写不敏感比对，可以修改列的默认排序规则（建表时也可直接指定）：

ALTER TABLE Table_1 ALTER COLUMN "Address" TYPE text COLLATE und_ci;
ALTER TABLE Table_2 ALTER COLUMN "People Address" TYPE text COLLATE und_ci;

修改完成后，直接使用你最初的EXCEPT语句就能自动实现大小写不敏感比对。

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