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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers