Postgres 12 case-insensitive compare

Question

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

Answer 1

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.

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