CODEWITHSUNDEEP
postgresqllocalecollationcase-insensitiveicu
Ziloni
Ziloni

Reputation: 101

Postgres 12 case-insensitive compare

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.

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

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"));

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

Is this correct?

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

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

Thanks in advance. PG

Upvotes: 10

Views: 5938

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246443

You got it right. From PostgreSQL v15 on, ICU collations can be used as database collations, but only deterministic ones (that don't compare different strings as equal). So your case-insensitive collation wouldn't work there either. Since you are using v12, you cannot use ICU collations as database default collation at all, but have to use them in column definitions.

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

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

Upvotes: 8

Related Questions