clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591639 fiddles created (45706 in the last week).

-- Set up test for the following question on DBA.StackExchange: -- PostgreSQL nondeterministic collations are not supported for LIKE -- https://dba.stackexchange.com/a/266144/30859 CREATE COLLATION ci (provider = icu, locale = 'tr_TR', deterministic = false);
 hidden batch(es)


create table testtable1 ( id serial primary key, name text COLLATE "ci" );
 hidden batch(es)


insert into testtable1 values(3,'abc');
1 rows affected
 hidden batch(es)


-- Same error as shown in question: select name from testtable1 WHERE name LIKE '%a%'
ERROR: nondeterministic collations are not supported for LIKE
 hidden batch(es)


-- Same locale used to create custom collation might not exist for encoding: select name from testtable1 WHERE name LIKE '%a%' COLLATE "tr_TR"
ERROR: collation "tr_TR" for encoding "UTF8" does not exist LINE 2: select name from testtable1 WHERE name LIKE '%a%' COLLATE "t... ^
 hidden batch(es)


-- Check what collations are available for UTF-8 encoding: select * from pg_collation where collname like '%utf8'
oid collname collnamespace collowner collprovider collisdeterministic collencoding collcollate collctype collversion
12327 C.utf8 11 10 c t 6 C.utf8 C.utf8
12330 en_AU.utf8 11 10 c t 6 en_AU.utf8 en_AU.utf8
12332 en_BW.utf8 11 10 c t 6 en_BW.utf8 en_BW.utf8
12334 en_CA.utf8 11 10 c t 6 en_CA.utf8 en_CA.utf8
12336 en_DK.utf8 11 10 c t 6 en_DK.utf8 en_DK.utf8
12339 en_GB.utf8 11 10 c t 6 en_GB.utf8 en_GB.utf8
12341 en_HK.utf8 11 10 c t 6 en_HK.utf8 en_HK.utf8
12344 en_IE.utf8 11 10 c t 6 en_IE.utf8 en_IE.utf8
12349 en_NZ.utf8 11 10 c t 6 en_NZ.utf8 en_NZ.utf8
12351 en_PH.utf8 11 10 c t 6 en_PH.utf8 en_PH.utf8
12352 en_SC.utf8 11 10 c t 6 en_SC.utf8 en_SC.utf8
12354 en_SG.utf8 11 10 c t 6 en_SG.utf8 en_SG.utf8
12357 en_US.utf8 11 10 c t 6 en_US.utf8 en_US.utf8
12359 en_ZA.utf8 11 10 c t 6 en_ZA.utf8 en_ZA.utf8
12362 en_ZW.utf8 11 10 c t 6 en_ZW.utf8 en_ZW.utf8
 hidden batch(es)


-- Check if there are any "tr" collations: select * from pg_collation where collname like '%tr%'
oid collname collnamespace collowner collprovider collisdeterministic collencoding collcollate collctype collversion
13038 tr-x-icu 11 10 i t -1 tr tr 153.80.32.1
13039 tr-CY-x-icu 11 10 i t -1 tr-CY tr-CY 153.80.32.1
13040 tr-TR-x-icu 11 10 i t -1 tr-TR tr-TR 153.80.32.1
 hidden batch(es)


-- The icu-supplied collation for the tr-TR locale does work: select name from testtable1 WHERE name LIKE '%a%' COLLATE "tr-TR-x-icu"
name
abc
 hidden batch(es)