Postgres 12大小写不敏感比较

Question

我试图将一个被C#应用程序（+EF6）使用的SQL Server数据库转移到Postgres 12，但我在使大小写不敏感的字符串比较方面并没有太大的成功。现有的SQL Server数据库使用SQL_Latin1_General_CP1_CI_AS排序规则，这意味着所有WHERE子句都不必担心大小写。

我知道以前使用CIText是实现这一目的的方法，但现在已经被非确定性排序规则所取代。

我创建了这样的排序规则；

CREATE COLLATION ci (provider = icu, locale = 'und-u-ks-level2', deterministic = false);

当应用于CREATE TABLE的每个列时，它确实起作用 - 不区分大小写。

CREATE TABLE casetest (
id serial NOT NULL,
code varchar(10) null COLLATE "ci",
CONSTRAINT "PK_id" PRIMARY KEY ("id"));

但是根据我所了解的，它必须应用于每个varchar列，不能在整个数据库中全局设置。

这是正确的吗？

我不想到处使用.ToLower()处理，因为这样会导致杂乱无章，而且任何列上的索引也不会被使用。

我尝试修改pg_collation中预先存在的“default”排序规则，使其与“ci”排序规则的设置相匹配，但没有效果。

提前谢谢。

PG

- Ziloni

Answer 1

你说得对。从 PostgreSQL v15 开始，可以使用 ICU 排序作为数据库排序，但仅限确定性排序（不将不同的字符串视为相等）。因此，您的不区分大小写排序也无法在此处工作。由于您正在使用 v12，因此根本无法将 ICU 排序用作数据库默认排序，但必须在列定义中使用它们。

这种限制很烦人，与事物的本质不符。它可能会在某个未来版本中被解除。

您可以使用 DO 语句更改所有字符串列的排序：

DO
$$DECLARE
   v_table  regclass;
   v_column name;
   v_type   oid;
   v_typmod integer;
BEGIN
   FOR v_table, v_column, v_type, v_typmod IN
      SELECT a.attrelid::regclass,
             a.attname,
             a.atttypid,
             a.atttypmod
      FROM pg_attribute AS a
         JOIN pg_class AS c ON a.attrelid = c.oid
      WHERE a.atttypid IN (25, 1042, 1043)
        AND c.relnamespace::regnamespace::name
            NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
   LOOP
      EXECUTE
         format('ALTER TABLE %s ALTER %I SET DATA TYPE %s COLLATE ci',
                v_table,
                v_column,
                format_type(v_type, v_typmod)
         );
   END LOOP;
END;$$;

- Laurenz Albe

---

在PostgreSQL Commitfest 2019-11中，这个功能被“带着反馈”退回了。所以我猜它不会出现了。 - cachius

@ReinState 未来可能会有另一个补丁来解决这个问题；人们已经意识到这是一个缺陷。 - Laurenz Albe

这似乎是商业版“EDB Postgres Advanced Server”的一个功能。您可以像 CREATE DATABASE collation_db TEMPLATE template0 ENCODING 'UTF8' ICU_SHORT_FORM = 'AN_CU_EX_NX_LROOT'; 这样为每个数据库设置默认的ICU排序规则。在旧文档中有很好的例子（其他版本和格式）。但没有提到不确定性排序规则。 - cachius

如果您希望查询在不区分大小写的情况下正常工作，可以使用此脚本将列类型转换为citext。这是一个附加的提供模块，在进行查询之前调用lower()。通过CREATE EXTENSION citext;导入一次，并将格式行更改为format('ALTER TABLE %s ALTER COLUMN %I SET DATA TYPE CITEXT'。这样做的好处是LIKE、ILIKE和~* 仍然有效，并且通过LIKE语句启用可移植SQL。 - cachius

这在第15版中仍然适用吗？ - dipold

@dipold 在v15中，您可以使用ICU排序作为数据库排序，但不能使用不区分大小写的排序。 - Laurenz Albe