We are migrating an application from SQL Server to Postgres and attempting to emulate various aspects of the case insensitivity of SQL Server. We have created a non-deterministic collation to support case-insensitive matching of foreign keys and equality comparisons.

But we are seeing some weird behaviour when using ILIKE which we can't explain, and would appreciate some assistance.

To see the behaviour, run the following on a fresh database:

CREATE COLLATION IF NOT EXISTS public.ci (provider = icu, locale = 'und-u-ks-level2', deterministic = false);

DROP TABLE IF EXISTS sort_test;

CREATE TABLE sort_test (a text COLLATE public.ci);

INSERT INTO sort_test SELECT md5(n::text) FROM generate_series(1, 10000) n;

-- Removing the following line fixes the issue
ANALYZE sort_test;

-- This line throws "nondeterministic collations are not supported for ILIKE"
SELECT * FROM sort_test WHERE a ILIKE 'c4ca4238a0%' COLLATE "und-x-icu";

Why does running the ANALYZE statement break the ILIKE statement?