1

I posted this on stack overflow as well, but didn't get much traction over there. If I am not able to get some thoughts on here, then I will probably open a support ticket with AWS.

I am trying to set the collation on AWS Aurora Postgres 12 to a user defined collation type.

CREATE COLLATION ndcoll (provider = icu, locale = 'und', deterministic = false);

I am able to create the collation just fine. It ends up being entered into the pg_collations table.

However, when I go to try to create a database using this new user defined collation, I keep receiving the error SQL Error [42809]: ERROR: invalid locale name: "ndcoll"

This is the create database statement:

create database "foo" with template 'bar' lc_collate = "ndcoll";

I know there is also a way to set the collation at the server level using a custom parameter group. But the parameter group family I am using aurora-postgres 12 does not have a parameter available to set the collation. After creating my user defined collation, I restarted the Aurora server, but still cannot get the DB to recognize the locale. I might be not understanding locale and lc_collate as well. Maybe there is a misunderstanding on my end fundamentally.

Does anyone have any insight as to how I can use my user defined collation either 1) to create new databases using this user defined collation or 2) set the collation at the server level to a non-deterministic collation in the aurora-postgres12 parameter group family.

Thanks in advance!

3
  • Could you try this syntax: ´create database foo template 'bar' locale = 'ndcoll';´ Have the template 'bar' the same collation?
    – McNets
    Oct 15 '21 at 15:15
  • Hey there, if I change to locale = 'ndcoll' I get the error: SQL Error [42601]: ERROR: option "locale" not recognized. That's a good point, the template DB is not the same collation as the DB I am trying to create. But removing the template DB parameter, I am running this: CREATE DATABASE "foo" lc_collate = "ndcoll"; and recieving the original error from above. i.e. invalid locale name. Oct 15 '21 at 20:56
  • According to PostgreSQL docs: The encoding and locale settings must match those of the template database, except when template0 is used as template
    – McNets
    Oct 16 '21 at 10:10
1

The locale parameter of CREATE DATABASE expects a name of locale that can be recognized and used by the setlocale POSIX function of the underlying operating system, or whatever is the equivalent if the OS is Windows.

On the other hand, the names of collations that are user-defined by CREATE COLLATION and whose scope is only the current database do not have to match anything in particular. Sometimes they happen to coincide with the name of the associated locale, but they don't have to, such as in your choice of ndcoll.

That is why the CREATE DATABASE fails with ERROR: invalid locale name: "ndcoll"

If ICU locales were supported by PostgreSQL for the default collation of a database, und could be used as the locale argument, and some other argument would indicate that the locale provider is ICU.

However, they are not supported in any current version of PostgreSQL (up to and including version 14), so it's not possible anyway, unless Amazon implemented this in Aurora.

1
  • Ok interesting, I will try something new then today. It's bizarre to me then that the collation was created if the ICU provider is in fact not supported by postgres. It's also confusing to me why on the documentation page that I used as an example, ICU was used as the provider parameter for the CREATE COLLATION statement example if it's not supported... But thank you I will let you know how it goes today. Oct 18 '21 at 13:46
0

I received an answer on SO.

You cannot use non-deterministic collations at a database level in Postgres 12. You can only use non-deterministic collations in column definitions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.