3

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.

1 Answer 1

2

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)
1
  • Your suggestion works very well, Laurenz. Thanks. Jan 30 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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