Postgres 11

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

SELECT upper('é' collate "C") AS "C", upper('é' collate "C.UTF-8") AS "C.UTF-8", upper('é' collate "ucs_basic") AS "ucs_basic";

ERROR:  collation "C.UTF-8" for encoding "UTF8" does not exist
LINE 1: SELECT upper('é' collate "C") AS "C", upper('é' collate "C.U...
                                                        ^


SELECT 'A' AS "0041", E'\u0378' AS "0378", ('A' < E'\u0378' collate "C") AS "C", ('A' < E'\u0378' collate "C.UTF-8") AS "C.UTF-8", ('A' < E'\u0378' collate "ucs_basic") AS "ucs_basic", ('A' < E'\u0378' collate "en_GB") AS "en_GB";

ERROR:  collation "C.UTF-8" for encoding "UTF8" does not exist
LINE 1: ...< E'\u0378' collate "C") AS "C", ('A' < E'\u0378' collate "C...
                                                             ^


SELECT 'A' AS "0041", E'\u0377' AS "0377", ('A' < E'\u0377' collate "C") AS "C", ('A' < E'\u0377' collate "C.UTF-8") AS "C.UTF-8", ('A' < E'\u0377' collate "ucs_basic") AS "ucs_basic", ('A' < E'\u0377' collate "en_GB") AS "en_GB";

ERROR:  collation "C.UTF-8" for encoding "UTF8" does not exist
LINE 1: ...< E'\u0377' collate "C") AS "C", ('A' < E'\u0377' collate "C...
                                                             ^


SELECT E'\u00E9' AS "00E9", E'\u00D3' AS "00D3", (E'\u00E9' < E'\u00D3' collate "C") AS "C", (E'\u00E9' < E'\u00D3' collate "C.UTF-8") AS "C.UTF-8", (E'\u00E9' < E'\u00D3' collate "ucs_basic") AS "ucs_basic", (E'\u00E9' < E'\u00D3' collate "en_GB") AS "en_GB";

ERROR:  collation "C.UTF-8" for encoding "UTF8" does not exist
LINE 1: ...00D3' collate "C") AS "C", (E'\u00E9' < E'\u00D3' collate "C...
                                                             ^


SELECT E'\u0510' AS "0510", E'\u08BA' AS "08BA", (E'\u0510' < E'\u08BA' collate "C") AS "C", (E'\u0510' < E'\u08BA' collate "C.UTF-8") AS "C.UTF-8", (E'\u0510' < E'\u08BA' collate "ucs_basic") AS "ucs_basic", (E'\u0510' < E'\u08BA' collate "en_GB") AS "en_GB";

ERROR:  collation "C.UTF-8" for encoding "UTF8" does not exist
LINE 1: ...08BA' collate "C") AS "C", (E'\u0510' < E'\u08BA' collate "C...
                                                             ^


SELECT ('ab' < 'a-c' collate "C") AS "C", ('ab' < 'a-c' collate "C.UTF-8") AS "C.UTF-8", ('ab' < 'a-c' collate "ucs_basic") AS "ucs_basic", ('ab' < 'a-c' collate "en_GB") AS "en_GB";

ERROR:  collation "C.UTF-8" for encoding "UTF8" does not exist
LINE 1: ... ('ab' < 'a-c' collate "C") AS "C", ('ab' < 'a-c' collate "C...
                                                             ^


select * from pg_collation where collprovider='c' order by "collname";


collname
collnamespace
collowner
collprovider
collencoding
collcollate
collctype
collversion


C
11
10
c
-1
C
C
null

C.utf8
11
10
c
6
C.utf8
C.utf8
null

POSIX
11
10
c
-1
POSIX
POSIX
null

en_AG
11
10
c
6
en_AG
en_AG
null

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

en_AU
11
10
c
8
en_AU
en_AU
null

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

en_BW
11
10
c
8
en_BW
en_BW
null

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

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

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

en_CA
11
10
c
8
en_CA
en_CA
null

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

en_DK
11
10
c
8
en_DK
en_DK
null

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

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

en_GB
11
10
c
8
en_GB
en_GB
null

en_GB
11
10
c
16
en_GB.iso885915
en_GB.iso885915
null

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

en_GB.iso885915
11
10
c
16
en_GB.iso885915
en_GB.iso885915
null

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

en_HK
11
10
c
8
en_HK
en_HK
null

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

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

en_IE
11
10
c
8
en_IE
en_IE
null

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

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

en_IE@euro
11
10
c
16
en_IE@euro
en_IE@euro
null

en_IL
11
10
c
6
en_IL
en_IL
null

en_IN
11
10
c
6
en_IN
en_IN
null

en_NG
11
10
c
6
en_NG
en_NG
null

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

en_NZ
11
10
c
8
en_NZ
en_NZ
null

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

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

en_PH
11
10
c
8
en_PH
en_PH
null

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

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

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

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

en_SG
11
10
c
8
en_SG
en_SG
null

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

en_US
11
10
c
16
en_US.iso885915
en_US.iso885915
null

en_US
11
10
c
8
en_US
en_US
null

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

en_US.iso885915
11
10
c
16
en_US.iso885915
en_US.iso885915
null

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

en_US@ampm
11
10
c
6
en_US@ampm.UTF-8
en_US@ampm.UTF-8
null

en_US@ampm.UTF-8
11
10
c
6
en_US@ampm.UTF-8
en_US@ampm.UTF-8
null

en_ZA
11
10
c
8
en_ZA
en_ZA
null

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

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

en_ZM
11
10
c
6
en_ZM
en_ZM
null

en_ZW
11
10
c
8
en_ZW
en_ZW
null

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

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

ucs_basic
11
10
c
6
C
C
null



SELECT 57

collname	collnamespace	collowner	collprovider	collencoding	collcollate	collctype	collversion
C	11	10	c	-1	C	C	null
C.utf8	11	10	c	6	C.utf8	C.utf8	null
POSIX	11	10	c	-1	POSIX	POSIX	null
en_AG	11	10	c	6	en_AG	en_AG	null
en_AU	11	10	c	6	en_AU.utf8	en_AU.utf8	null
en_AU	11	10	c	8	en_AU	en_AU	null
en_AU.utf8	11	10	c	6	en_AU.utf8	en_AU.utf8	null
en_BW	11	10	c	8	en_BW	en_BW	null
en_BW	11	10	c	6	en_BW.utf8	en_BW.utf8	null
en_BW.utf8	11	10	c	6	en_BW.utf8	en_BW.utf8	null
en_CA	11	10	c	6	en_CA.utf8	en_CA.utf8	null
en_CA	11	10	c	8	en_CA	en_CA	null
en_CA.utf8	11	10	c	6	en_CA.utf8	en_CA.utf8	null
en_DK	11	10	c	8	en_DK	en_DK	null
en_DK	11	10	c	6	en_DK.utf8	en_DK.utf8	null
en_DK.utf8	11	10	c	6	en_DK.utf8	en_DK.utf8	null
en_GB	11	10	c	8	en_GB	en_GB	null
en_GB	11	10	c	16	en_GB.iso885915	en_GB.iso885915	null
en_GB	11	10	c	6	en_GB.utf8	en_GB.utf8	null
en_GB.iso885915	11	10	c	16	en_GB.iso885915	en_GB.iso885915	null
en_GB.utf8	11	10	c	6	en_GB.utf8	en_GB.utf8	null
en_HK	11	10	c	8	en_HK	en_HK	null
en_HK	11	10	c	6	en_HK.utf8	en_HK.utf8	null
en_HK.utf8	11	10	c	6	en_HK.utf8	en_HK.utf8	null
en_IE	11	10	c	8	en_IE	en_IE	null
en_IE	11	10	c	6	en_IE.utf8	en_IE.utf8	null
en_IE.utf8	11	10	c	6	en_IE.utf8	en_IE.utf8	null
en_IE@euro	11	10	c	16	en_IE@euro	en_IE@euro	null
en_IL	11	10	c	6	en_IL	en_IL	null
en_IN	11	10	c	6	en_IN	en_IN	null
en_NG	11	10	c	6	en_NG	en_NG	null
en_NZ	11	10	c	6	en_NZ.utf8	en_NZ.utf8	null
en_NZ	11	10	c	8	en_NZ	en_NZ	null
en_NZ.utf8	11	10	c	6	en_NZ.utf8	en_NZ.utf8	null
en_PH	11	10	c	6	en_PH.utf8	en_PH.utf8	null
en_PH	11	10	c	8	en_PH	en_PH	null
en_PH.utf8	11	10	c	6	en_PH.utf8	en_PH.utf8	null
en_SC	11	10	c	6	en_SC.utf8	en_SC.utf8	null
en_SC.utf8	11	10	c	6	en_SC.utf8	en_SC.utf8	null
en_SG	11	10	c	6	en_SG.utf8	en_SG.utf8	null
en_SG	11	10	c	8	en_SG	en_SG	null
en_SG.utf8	11	10	c	6	en_SG.utf8	en_SG.utf8	null
en_US	11	10	c	16	en_US.iso885915	en_US.iso885915	null
en_US	11	10	c	8	en_US	en_US	null
en_US	11	10	c	6	en_US.utf8	en_US.utf8	null
en_US.iso885915	11	10	c	16	en_US.iso885915	en_US.iso885915	null
en_US.utf8	11	10	c	6	en_US.utf8	en_US.utf8	null
en_US@ampm	11	10	c	6	en_US@ampm.UTF-8	en_US@ampm.UTF-8	null
en_US@ampm.UTF-8	11	10	c	6	en_US@ampm.UTF-8	en_US@ampm.UTF-8	null
en_ZA	11	10	c	8	en_ZA	en_ZA	null
en_ZA	11	10	c	6	en_ZA.utf8	en_ZA.utf8	null
en_ZA.utf8	11	10	c	6	en_ZA.utf8	en_ZA.utf8	null
en_ZM	11	10	c	6	en_ZM	en_ZM	null
en_ZW	11	10	c	8	en_ZW	en_ZW	null
en_ZW	11	10	c	6	en_ZW.utf8	en_ZW.utf8	null
en_ZW.utf8	11	10	c	6	en_ZW.utf8	en_ZW.utf8	null
ucs_basic	11	10	c	6	C	C	null