postgreSQL collation problem when comparing a column value with a given value

Question

Assume that we created a table and inserted a record with the following script.

CREATE TABLE your_table ( your_column VARCHAR COLLATE "tr-TR-x-icu" );
INSERT INTO your_table(your_column) VALUES ('Gider');

When I executed the following revised script I got the 'not equal' as a result. The result should be 'equal' since I use "tr-TR-x-icu" collation of the column and the upper of the text 'Gider' is 'GİDER' in Turkish.

SELECT 
CASE WHEN UPPER(your_column)=UPPER('Gider') THEN 'equal' 
 ELSE 'not equal' 
END AS result 
FROM your_table;

When I executed the following script I got the correct answer (i.e. 'equal').

SELECT 
CASE WHEN UPPER(your_column)=UPPER('Gider' COLLATE "tr-TR-x-icu") THEN 'equal' 
 ELSE 'not equal' 
END AS result 
FROM your_table;

It would be nice if there is method of setting this collate property inside this session.

For example, adding like SET COLLATE... right before the query.

In this way, instead of writing UPPER(your_column)=UPPER('Gider' COLLATE "tr-TR-x-icu"), it would be sufficient to just write UPPER(your_column)=UPPER('Gider').

I would be happy if anyone could offer a solution.
Best wishes.

Answer 1

If you want the default collation to be tr-TR-x-icu, create your database with the appropriate ICU collation (from v15 on):

CREATE DATABASE turkish
 TEMPLATE template0
 LOCALE_PROVIDER icu
 ICU_LOCALE "tr-TR"
 LOCALE "tr_TR.utf8";

Then connect to the database, and it will behave like you want:

SELECT upper('Gider'), lower('KADIN');
 upper │ lower 
═══════╪═══════
 GİDER │ kadın
(1 row)

• Orhan Sönmez: Your suggestion works very well, Laurenz. Thanks.