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

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.

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)

StackExchange Database Administrators Q#335234, answer score: 2