agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19681: ILIKE rejected on nondeterministic collations while LIKE works
2+ messages / 2 participants
[nested] [flat]
* BUG #19681: ILIKE rejected on nondeterministic collations while LIKE works
@ 2026-09-08 07:05 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-09-08 07:05 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 303677365@qq.com
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:
On a column with a nondeterministic (case-insensitive) collation, the
pattern-matching operators split into two contradictory groups:
CREATE COLLATION ci (provider = icu, locale = 'und-u-ks-level2',
deterministic = false);
CREATE TABLE ndt(b text COLLATE ci);
INSERT INTO ndt VALUES ('a'),('A'),('str'),('STR'),('Hello');
Group 1 — 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 = '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 = 'STR'; -- true
(documented example form)
Group 2 — 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 — 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 — the other case-insensitive subsystem
— accepts ILIKE without error on its columns, making the ND-collation
rejection of the same operator even more inconsistent.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19681: ILIKE rejected on nondeterministic collations while LIKE works
@ 2026-09-12 15:50 Nitin Motiani <nitinmotiani@google.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Nitin Motiani @ 2026-09-12 15:50 UTC (permalink / raw)
To: 303677365@qq.com; pgsql-bugs@lists.postgresql.org
On Thu, Sep 10, 2026 at 5:38 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
>
> The documentation (func-matching.sgml) states that LIKE supports
> nondeterministic collations and that SIMILAR TO / POSIX regex do not — but
> says nothing about ILIKE. Meanwhile ILIKE's documented definition is exactly
I see that this doc
https://www.postgresql.org/docs/18/functions-matching.html#FUNCTIONS-LIKE
says that ILIKE doesn't support non-deterministic-collations.
"The key word ILIKE can be used instead of LIKE to make the match
case-insensitive according to the active locale. (But this does not
support nondeterministic collations.)"
So this looks like it is working as intended.
Thanks
Nitin Motiani
Senior Software Engineer,
Google
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-09-12 15:50 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-08 07:05 BUG #19681: ILIKE rejected on nondeterministic collations while LIKE works PG Bug reporting form <noreply@postgresql.org>
2026-09-12 15:50 ` Nitin Motiani <nitinmotiani@google.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox