public inbox for [email protected]  

help / color / mirror / Atom feed

From: Jeff Davis <[email protected]>
To: Thom Brown <[email protected]>
To: Peter Eisentraut <[email protected]>
Cc: Vik Fearing <[email protected]>
Cc: Joe Conway <[email protected]>
Cc: Ian Lawrence Barwick <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: Add CASEFOLD() function.
Date: Thu, 19 Jun 2025 09:33:41 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAA-aLv57dEeoX__Osx4k2yda6G2V-i=MYxr0HrH6U+yyYw5wKQ@mail.gmail.com>
References: <[email protected]>
	<CAB8KJ=jgnpbDTJS+jOQMzo7b1mPTwM0hCqQBBRgoX_kvyxWVog@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CAA-aLv7KLoT9yCdiJwRP9PeL_4yNTzQ3T8WJLbTtX=Ld45UOpg@mail.gmail.com>
	<[email protected]>
	<CAA-aLv57dEeoX__Osx4k2yda6G2V-i=MYxr0HrH6U+yyYw5wKQ@mail.gmail.com>

On Thu, 2025-06-19 at 16:36 +0100, Thom Brown wrote:
> Ease of use, perhaps. It seems easier to use:
> 
> column_name cftext
> 
> rather than:
> 
> CREATE COLLATION case_insensitive_collation (
>     PROVIDER = icu,
>     LOCALE = 'und-u-ks-level2',
>     DETERMINISTIC = FALSE
> );

We could auto-create such a collation at initdb time for ICU-enabled
builds.

> But I see the arguments against it. It creates an unnecessary
> dependency on an extension, and if someone wants to ignore both case
> and accents, they may resort to using 2 extensions (citext +
> unaccent)
> when none are needed.

There are at least three ways to do case insensitivity (or other kinds
of equivalence):

* Explicit function calls in queries, as well as index and constraint
definitions. E.g. expression index on LOWER(), queries that explicitly
do "LOWER(x) = ..."

* Wrap those function calls up in a separate data type, like citext.

* Non-deterministic collations.

Given that we have collations, which are a way of organizing alternate
behaviors for existing data types, I'm not sure I see the need for
creating an entirely separate data type.

> I guess I don't feel strongly about it either
> way.

Are you a user of citext? I'm genuinely interested in the use cases,
and whether the separate-data-type approach has merits that are missing
in the other approaches.

Regards,
	Jeff Davis

view thread (40+ messages)  latest in thread

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: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Add CASEFOLD() function.
  In-Reply-To: <[email protected]>

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

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