Postgres 9.6

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 table ithelp_mssql_20190410
(dataall varchar(50),
 col_b varchar(50));
insert into ithelp_mssql_20190410(dataall,col_b) 
values
('19930302','A'),
('19990312','B'),
('20030615','B'),
('20051112','B'),
('20090901','C'),
('20150130','A')
6 rows affected

select *,
CASE 
WHEN LENGTH(c)=2 THEN '0'||c
WHEN LENGTH(c)=3 THEN      c END d
from(
select
        DATAALL                    ,
 LEFT(DATAALL,4)                  a,
 LEFT(DATAALL,4)::INT-1911        b,
(LEFT(DATAALL,4)::INT-1911)::text c
from ithelp_mssql_20190410
)t


dataall
a
b
c
d


19930302
1993
82
82
082

19990312
1999
88
88
088

20030615
2003
92
92
092

20051112
2005
94
94
094

20090901
2009
98
98
098

20150130
2015
104
104
104

dataall	a	b	c	d
19930302	1993	82	82	082
19990312	1999	88	88	088
20030615	2003	92	92	092
20051112	2005	94	94	094
20090901	2009	98	98	098
20150130	2015	104	104	104