Postgres 12不区分大小写的比较 [英] Postgres 12 case-insensitive compare

问题描述

我正在尝试将C#应用程序(+ EF6)使用的SQL Server数据库移动到Postgres 12，但是我对使用不区分大小写的字符串比较工作没有多大运气.现有的SQL Server数据库使用SQL_Latin1_General_CP1_CI_AS排序规则，这意味着所有WHERE子句都不必担心大小写.

I'm attempting to move a SQL Server DB which is used by a C# application (+EF6) to Postgres 12 but I'm not having much luck with getting case-insensitive string comparisons working. The existing SQL Server db uses SQL_Latin1_General_CP1_CI_AS collation which means all WHERE clauses don't have to worry about case.

我知道CIText以前是这样做的，但是现在被非确定性排序规则所取代.

I understand that CIText was the way to do this previously, but is now superseded by non-deterministic collations.

我创建了这样的归类；

I created such a collation;

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

，并且将其应用于每个列的CREATE TABLE时，它确实起作用-大小写将被忽略.

and when this is applied to the CREATE TABLE on a per-column basis it does work - case is ignored.

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

但是根据我的阅读，它必须应用于每个varchar列，并且不能在整个数据库中进行全局设置.

But from what I have read it must be applied to every varchar column and can't be set globally across the whole db.

这是正确的吗?

由于混乱，我不想在任何地方都使用.ToLower()，然后不使用列上的任何索引.

I don't want to use .ToLower() everywhere due to clutter and that any index on the column is then not used.

我尝试修改pg_collat​​ion中预先存在的默认"排序规则以匹配"ci"排序规则的设置，但是它没有任何作用.

I tried modifying the pre-existing 'default' collation in pg_collation to match the settings of 'ci' collation but it has no effect.

先谢谢了.PG

推荐答案

您说对了.还不能将ICU归类用作数据库默认归类，而必须在列定义中使用.

You got it right. ICU collations cannot be used as database default collation (yet), but have to be used in column definitions.

此限制很烦人，不是事物的本质.将来的某些版本中可能会取消它.

This limitation is annoying and not in the nature of things. It will probably be lifted in some future version.

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

You can use a DO statement to change the collation of all string columns:

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;$$;

这篇关于Postgres 12不区分大小写的比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！