How to change locale

[vabatta]

Hi,

I am using money type and was wondering how can I change the lc_monetary to a different value than en_US as there are no other locales available.

select * 
from pg_collation
where collname ~ any(array['DE', 'CH'])

[megacherry]

I just stumbled upon the same question. I wanted to change the language via lc_time, unfortunately no other locales seem to be available.

For example
SET LC_TIME = 'fr_FR.UTF-8'
could not be executed.

show LC_TIME
shows en_US.UTF-8

@kiwicopple: Can you shed some more light on the darkness here? I store, as a workaround, my own locales.

[ghost]

I created a pull request to fix this related issue - supabase/cli#825

From the Postgres docs:

The drawback of using locales other than C or POSIX in PostgreSQL is its performance impact. It slows character handling and prevents ordinary indexes from being used by LIKE. For this reason use locales only if you actually need them.

https://www.postgresql.org/docs/current/locale.html#id-1.6.11.3.5

[cfjohan]

I'm facing this issue too, any new developments? Even if performance suffers it could be worth it to some users.

In case anyone else has this issue, I did some digging and found a workaround. I'm still learning supabase and postgres so maybe there are better ways, but here's what I did:

The supabase instance doesn't seem to have (m)any locales apart from en_US, but there are ICU collations available. You can find them by querying the pg_collation table and then create a collation:

SELECT collname, collprovider, collcollate FROM pg_collation WHERE collprovider = 'i' order by collname;

The collname is what you need to create the collation.

CREATE COLLATION new_collation (provider = icu, locale = 'chosen_collname', deterministic = false);

After that you can create tables with collate new_collation appended to a column.

This seems to work for sorting with ORDER BY at least.

[pidanou]

Would be very interested to know also if it is possible to change the postgres locale, for example "lc_messages" to be able to display the errors in french for example.