Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

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

CREATE COLLATION

CREATE COLLATION

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

CREATE TABLE

insert into testtable1 values(3, 'abc', 'abc');

INSERT 0 1

-- 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

SELECT 1

-- 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

SELECT 0

-- 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

SELECT 1

-- 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

SELECT 0

-- 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

SELECT 1

-- 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

SELECT 0