pg.ddx.io  pgsql-docs@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Jeff Davis <pgsql@j-davis.com>
To: Will Mortensen <will@extrahop.com>
To: pgsql-docs@lists.postgresql.org, Jeremy Schneider <schneider@ardentperf.com>
Cc: Daniel Verite <daniel@manitou-mail.org>
Subject: Re: Documenting more pitfalls of non-default collations?
Date: Tue, 11 Jun 2024 10:58:54 -0700
Message-ID: <23342b9d538adeab472217fd6a96c75b48a659fb.camel@j-davis.com> (raw)
In-Reply-To: <CAMpnoC6sJ9=F6OiXJ4kKyrHUzwbWXjUKC3t-rL4FrDyhyiYf5A@mail.gmail.com>
References: <CAMpnoC6sJ9=F6OiXJ4kKyrHUzwbWXjUKC3t-rL4FrDyhyiYf5A@mail.gmail.com>

On Mon, 2024-06-10 at 23:55 -0700, Will Mortensen wrote:
> I mentioned to Jeremy at pgConf.dev that using non-default collations
> in some SQL idioms can produce undesired results, and he asked me to
> send an email. An example idiom is the way Django implements
> case-insensitive comparisons using "upper(x) = upper(y)" [1][2][3] ,
> which returns false if x = y but they have different collations that
> produce different uppercase.

Hi,

Thank you for the examples.

There are quite a few subtleties to getting case-insensitive
comparisons right, and neither LOWER() nor UPPER() get everything quite
right even if the collation is the same.

For instance (for almost any locale other than "C"):

  UPPER(LOWER(U&'\1E9E')) != UPPER(U&'\1E9E')

And:

  LOWER(UPPER(U&'\03C2')) != LOWER(U&'\03C2')

The results of UPPER() and LOWER() can also change if some language
adds a new case variant in the future, which could be a problem if the
results are stored somewhere.

How should we document all of that? If we include too many caveats,
it's just frustrating.

Instead, I propose that we implement Unicode "case folding" in PG18,
which solves these issues by transforming the string to a canonical
form suitable for case-insensitive comparison. (In most cases, the
results are the same as LOWER(), but there are exceptions specifically
to avoid the problems above.)

Then, we can just have a section in the docs on "case folding" to
describe the right way to use it. That still leaves one caveat: the
handling of dotted- and dotless-i. But one caveat is a lot easier to
keep track of.

Regards,
	Jeff Davis






view thread (3+ messages)  latest in thread

Message-ID: <23342b9d538adeab472217fd6a96c75b48a659fb.camel@j-davis.com>
Permalink:  ../23342b9d538adeab472217fd6a96c75b48a659fb.camel@j-davis.com/
Also on:    postgresql.org/message-id/23342b9d538adeab472217fd6a96c75b48a659fb.camel@j-davis.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-docs@postgresql.org
  Cc: pgsql@j-davis.com, will@extrahop.com, schneider@ardentperf.com, daniel@manitou-mail.org
  Subject: Re: Documenting more pitfalls of non-default collations?
  In-Reply-To: <23342b9d538adeab472217fd6a96c75b48a659fb.camel@j-davis.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox