How to change locale for Vercel Postgres?

[FelixZY]

Summary

I'm using Vercel Postgres to store tables containing swedish names. For some queries, I want to ORDER BY these names:

SELECT
  name
FROM
  (
    VALUES
      ('Örjan'),
      ('Åsa'),
      ('Britt'),
      ('Niklas'),
      ('Ängla')
  ) AS names (name)
ORDER BY
  name;

In sweden, "Å", "Ä" and "Ö" are letters in their own right and should be ordered "ÅÄÖ" with placement at the end of the english alphabet. However, the above query yields the following, incorrect, result:

"Britt"
"Niklas"
"Ängla"
"Åsa"
"Örjan"

(specifically - the order should be "Åsa", "Ängla", "Örjan" rather than "Ängla", "Åsa", "Örjan").

While I could probably fix this by setting the collation per-column, there are a lot of columns and it's easy to forget to set the collation when adding/altering tables. It would be much easier if I could change the global locale to swedish. Is this supported by Vercel Postgres?

Example 1 - How I do it locally:

For the postgis/postgis docker container, I can set POSTGRES_INITDB_ARGS to change the locale

Example 2 - How to do it using CREATE DATABASE:

CREATE DATABASE "example_db"
WITH OWNER "postgres"
ENCODING 'UTF8'
LC_COLLATE = 'sv-SE'
LC_CTYPE = 'sv-SE';

(modified from original at https://stackoverflow.com/a/52067734/1137077)

A locale can be selected separately for each database. The SQL command CREATE DATABASE and its command-line equivalent createdb have options for that. Use this for example if a database cluster houses databases for multiple tenants with different requirements.

https://www.postgresql.org/docs/current/locale.html

Example

No response

Steps to Reproduce

SELECT
  name
FROM
  (
    VALUES
      ('Örjan'),
      ('Åsa'),
      ('Britt'),
      ('Niklas'),
      ('Ängla')
  ) AS names (name)
ORDER BY
  name;

[FelixZY]

Given that Vercel Postgres is powered by Neon in the background, I took a look at Neon's support site:

• https://community.neon.tech/t/can-we-set-the-locale-lc-collate-lc-ctype-of-a-database-other-than-the-default-c/311
• https://community.neon.tech/t/icu-provider-for-collation/263

A good first step is that it seems that we should be able to run CREATE DATABASE statements (which I was indeed able to do - I could not DROP DATABASE however, due to database "testdb" is being accessed by other users 🤔 ).

However, using SELECT * FROM pg_collation; to look up available collations returned a depressing result:

oid	collname	collnamespace	collowner	collprovider	collisdeterministic	collencoding	collcollate	collctype	colliculocale	collversion
100	default	11	10	d	true	-1
950	C	11	10	c	true	-1	C	C
951	POSIX	11	10	c	true	-1	POSIX	POSIX
12340	ucs_basic	11	10	c	true	6	C	C
12341	C.UTF-8	11	10	c	true	6	C.UTF-8	C.UTF-8

So while it is technically possible to create a new database with a specified collation, no collations beyond the default are really supported. In my case, I would need some variation of the sv-x-icu/sv-SE-x-icu collation to get proper sorting for swedish (including v/w which for some reason are grouped together when not using ICU).

[FelixZY]

Ok, I actually managed to solve it!

What you need to do is:

CREATE DATABASE
  mydb
WITH
  ENCODING 'UTF8'
  LOCALE_PROVIDER icu
  ICU_LOCALE "sv-SE"
  TEMPLATE template0;

Note: Vercel by default uses verceldb rather than mydb in its connection strings. I have not yet found a way to change this so you'll need to go through your secrets and point things right.

Vercel: I would really like a feature to change the target database in the auto-added connection string secrets.

[GustavoContreiras]

I'm looking for a solution for pt-BR.utf8. I can't use brazilian monetary type using Vercel's Postgres database on it's free version.

create collation public.pt_BR (LOCALE="pt_BR.utf8");

SQL Error [22023]: ERROR: could not create locale "pt_BR.utf8": No such file or directory
  Detail: The operating system could not find any locale data for the locale name "pt_BR.utf8".