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. 2591607 fiddles created (45731 in the last week).

CREATE COLLATION ci (provider = icu, locale = 'tr_TR', deterministic = false); CREATE COLLATION ci2 (provider = icu, locale = 'tr-TR-u-ks-level2', deterministic = false);
 hidden batch(es)


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


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


-- This doesn't error due to using the built-in collation which is deterministic: select name from testtable1 WHERE name LIKE '%a%' COLLATE "tr-TR-x-icu"
name
abc
 hidden batch(es)


-- This is case-sensitive since the built-in collation uses the default sensitivities: select name from testtable1 WHERE name LIKE '%A%' COLLATE "tr-TR-x-icu"
name
 hidden batch(es)


-- This is case-INsensitive only due to the ILIKE operator (instead of LIKE): select name from testtable1 WHERE name ILIKE '%A%' COLLATE "tr-TR-x-icu"
name
abc
 hidden batch(es)


-- This is case-sensitive since this column uses the collation created with -- the default sensitivities (i.e. case-sensitive): select name from testtable1 WHERE name = 'aBc';
name
 hidden batch(es)


-- This is case-INsensitive since this column uses the collation created with -- locale containing "-ks-level2": select name from testtable1 WHERE name2 = 'aBc';
name
abc
 hidden batch(es)


-- Even though this uses the case-INsensitive column, it is still case-sensitive -- due to overriding the column collation with the built-in collation which is -- case-sensitive (hence still need to use ILIKE for case-INsensitive matching): select name from testtable1 WHERE name2 LIKE '%A%' COLLATE "tr-TR-x-icu"
name
 hidden batch(es)