Postgres Pro
Downloads
Home   >   mailing lists

Thread: Creating a database with a non-predefined collation

Creating a database with a non-predefined collation

From
Shay Rojansky
Date:
Greetings hackers.

While working on first-class support for PG collations in the Entity Framework Core ORM, I've come across an interesting problem: it doesn't seem to be possible to create a database with a collation that isn't predefined, and there doesn't seem to be a way to add to that list. I'm specifically looking into creating a database with an ICU collation.

I've seen the discussion in https://www.postgresql.org/message-id/flat/99faa8eb-9de2-8ec0-0a25-1ad1276167cc%402ndquadrant.com, though to my understanding, that is about importing ICU rather than libc predefined collations, and not adding an arbitrary collation to the list from which databases can be created.

This seems particularly problematic as a database collation cannot be altered once created, leading to an odd chicken-and-egg problem. My initial expectation was for collations in the template database to be taken into account, but that doesn't seem to be the case.

Finally, just a word to say that better support for non-deterministic collations would be greatly appreciated - specifically LIKE support (though I realize that isn't trivial). At the moment their actual usefulness seems somewhat limited because of this.

Thanks,

Shay

Re: Creating a database with a non-predefined collation

From
Laurenz Albe
Date:
On Sun, 2020-03-29 at 23:31 +0200, Shay Rojansky wrote:
> While working on first-class support for PG collations in the Entity Framework Core ORM,
> I've come across an interesting problem: it doesn't seem to be possible to create a
> database with a collation that isn't predefined, and there doesn't seem to be a way to
> add to that list. I'm specifically looking into creating a database with an ICU collation.
> 
> I've seen the discussion in
> https://www.postgresql.org/message-id/flat/99faa8eb-9de2-8ec0-0a25-1ad1276167cc%402ndquadrant.com,
> though to my understanding, that is about importing ICU rather than libc predefined collations,
> and not adding an arbitrary collation to the list from which databases can be created.
> 
> This seems particularly problematic as a database collation cannot be altered once created,
> leading to an odd chicken-and-egg problem. My initial expectation was for collations in the
> template database to be taken into account, but that doesn't seem to be the case.

This is indeed a missing feature, and the thread you reference was trying to improve
things, but enough obstacles surfaced that it didn't make it.

It is less trivial that it looks at first glance.

Yours,
Laurenz Albe