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

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

CREATE COLLATION


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

CREATE TABLE


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

INSERT 0 1


-- Same error as shown in question:
select name from testtable1 WHERE name LIKE '%a%'

ERROR:  nondeterministic collations are not supported for LIKE


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


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

12330
en_AU.utf8
11
10
c
t
6
en_AU.utf8
en_AU.utf8
null

12332
en_BW.utf8
11
10
c
t
6
en_BW.utf8
en_BW.utf8
null

12334
en_CA.utf8
11
10
c
t
6
en_CA.utf8
en_CA.utf8
null

12336
en_DK.utf8
11
10
c
t
6
en_DK.utf8
en_DK.utf8
null

12339
en_GB.utf8
11
10
c
t
6
en_GB.utf8
en_GB.utf8
null

12341
en_HK.utf8
11
10
c
t
6
en_HK.utf8
en_HK.utf8
null

12344
en_IE.utf8
11
10
c
t
6
en_IE.utf8
en_IE.utf8
null

12349
en_NZ.utf8
11
10
c
t
6
en_NZ.utf8
en_NZ.utf8
null

12351
en_PH.utf8
11
10
c
t
6
en_PH.utf8
en_PH.utf8
null

12352
en_SC.utf8
11
10
c
t
6
en_SC.utf8
en_SC.utf8
null

12354
en_SG.utf8
11
10
c
t
6
en_SG.utf8
en_SG.utf8
null

12358
en_US.utf8
11
10
c
t
6
en_US.utf8
en_US.utf8
null

12360
en_ZA.utf8
11
10
c
t
6
en_ZA.utf8
en_ZA.utf8
null

12363
en_ZW.utf8
11
10
c
t
6
en_ZW.utf8
en_ZW.utf8
null



SELECT 15


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


13040
tr-x-icu
11
10
i
t
-1
tr
tr
153.80.32.1

13041
tr-CY-x-icu
11
10
i
t
-1
tr-CY
tr-CY
153.80.32.1

13042
tr-TR-x-icu
11
10
i
t
-1
tr-TR
tr-TR
153.80.32.1



SELECT 3


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



SELECT 1

oid	collname	collnamespace	collowner	collprovider	collisdeterministic	collencoding	collcollate	collctype	collversion
12327	C.utf8	11	10	c	t	6	C.utf8	C.utf8	null
12330	en_AU.utf8	11	10	c	t	6	en_AU.utf8	en_AU.utf8	null
12332	en_BW.utf8	11	10	c	t	6	en_BW.utf8	en_BW.utf8	null
12334	en_CA.utf8	11	10	c	t	6	en_CA.utf8	en_CA.utf8	null
12336	en_DK.utf8	11	10	c	t	6	en_DK.utf8	en_DK.utf8	null
12339	en_GB.utf8	11	10	c	t	6	en_GB.utf8	en_GB.utf8	null
12341	en_HK.utf8	11	10	c	t	6	en_HK.utf8	en_HK.utf8	null
12344	en_IE.utf8	11	10	c	t	6	en_IE.utf8	en_IE.utf8	null
12349	en_NZ.utf8	11	10	c	t	6	en_NZ.utf8	en_NZ.utf8	null
12351	en_PH.utf8	11	10	c	t	6	en_PH.utf8	en_PH.utf8	null
12352	en_SC.utf8	11	10	c	t	6	en_SC.utf8	en_SC.utf8	null
12354	en_SG.utf8	11	10	c	t	6	en_SG.utf8	en_SG.utf8	null
12358	en_US.utf8	11	10	c	t	6	en_US.utf8	en_US.utf8	null
12360	en_ZA.utf8	11	10	c	t	6	en_ZA.utf8	en_ZA.utf8	null
12363	en_ZW.utf8	11	10	c	t	6	en_ZW.utf8	en_ZW.utf8	null

oid	collname	collnamespace	collowner	collprovider	collisdeterministic	collencoding	collcollate	collctype	collversion
13040	tr-x-icu	11	10	i	t	-1	tr	tr	153.80.32.1
13041	tr-CY-x-icu	11	10	i	t	-1	tr-CY	tr-CY	153.80.32.1
13042	tr-TR-x-icu	11	10	i	t	-1	tr-TR	tr-TR	153.80.32.1