Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19681: ILIKE rejected on nondeterministic collations while LIKE
 works
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: 303677365@qq.com
Reply-To: 303677365@qq.com, pgsql-bugs@lists.postgresql.org
Date: Tue, 08 Sep 2026 07:05:18 +0000
Message-ID: <19681-074ed6100d92cc5b@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19681-074ed6100d92cc5b%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

Bug reference:      19681
Logged by:          chunling qin
Email address:      303677365@qq.com
PostgreSQL version: 18.6
Operating system:   x86_64
Description:       =20

On a column with a nondeterministic (case-insensitive) collation, the
pattern-matching operators split into two contradictory groups:
CREATE COLLATION ci (provider =3D icu, locale =3D 'und-u-ks-level2',
deterministic =3D false);
CREATE TABLE ndt(b text COLLATE ci);
INSERT INTO ndt VALUES ('a'),('A'),('str'),('STR'),('Hello');

Group 1 =E2=80=94 silently supported (documented for LIKE):
SELECT count(*) FROM ndt WHERE b LIKE 's%';          -- 2  (matches 'str'
and 'STR')
SELECT count(*) FROM ndt WHERE b LIKE 'str';         -- 2
SELECT count(*) FROM ndt WHERE b IN ('str');          -- 2
SELECT count(*) FROM ndt WHERE b =3D 'str';             -- 1
SELECT count(*) FROM ndt WHERE upper(b) LIKE 'S%';    -- 2  (upper+LIKE:
ILIKE's definition!)
SELECT b LIKE 'str' COLLATE ci FROM ndt WHERE b =3D 'STR';  -- true
(documented example form)

Group 2 =E2=80=94 rejected with an error:
SELECT count(*) FROM ndt WHERE b ILIKE 's%';          -- ERROR:
nondeterministic collations are not supported for ILIKE
SELECT b ILIKE 'str' COLLATE ci FROM ndt;            -- ERROR: same
SELECT count(*) FROM ndt WHERE b ~ '^s';              -- ERROR:
nondeterministic collations are not supported for regular expressions
SELECT count(*) FROM ndt WHERE b SIMILAR TO 's%';     -- ERROR: same

The documentation (func-matching.sgml) states that LIKE supports
nondeterministic collations and that SIMILAR TO / POSIX regex do not =E2=80=
=94 but
says nothing about ILIKE. Meanwhile ILIKE's documented definition is exactly
"matches LIKE, but case-insensitively" (implemented as case folding + LIKE),
and that folding path works fine on the same column (upper(b) LIKE 'S%'
returns 2 rows). The citext extension =E2=80=94 the other case-insensitive =
subsystem
=E2=80=94 accepts ILIKE without error on its columns, making the ND-collati=
on
rejection of the same operator even more inconsistent.