Re: Custom collations, collation modifiers (eg case-insensitive)

On 12/30/2013 08:49 AM, Craig Ringer wrote:
> One of the big appeals of the new COLLATE feature was, to me, the
> possibility that we'd be able to support custom collations including
> case-insensitive collations in future.
>
> It's something I'd like to tackle one day, and in the mean time want to
> pop on the TODO so it's not lost and forgotten. Everyone OK with that?
>
>
> [TODO] User-defined collations or collation modifiers/filters
>
> [TODO] Provide a built-in case-insensitive collation modifier, i.e.
> COLLATE ... CASE INSENSITIVE, or current-collation case insensitive as
> COLLATE CASE INSENSITIVE.

The SQL 20xx draft I'm working from does not appear to specify
subclauses to COLLATE, and vendors that use COLLATE for case insensitive
comparisions appear to do so with extended collation names. So any kind
of "CASE INSENSITIVE" modifier would be an extension.

It has CREATE CHARACTER SET (11.41) and CREATE COLLATION (11.43). CREATE
COLLATION offers control over space padding, but not case.

It might be better to just offer variants of all supported collations
that ignore (a) case, (b) case and accents, or (c) just accents. The
only problem with that is that you can't say "give me the case
insensitive variant of whatever the current locale's default collation
is", you have to specify the collation.

(I intensely dislike the idea of ignoring accents, but it's something
some people appear to need/want, and is supported by other vendors).

Anyway, I just wanted to raise this as a future TODO. Back to trying to
work out issues in updatable s.b. views.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services