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 version();
| version |
|---|
| PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit |
SELECT 1
create table sf1(date1 text, ticker text, revenue integer);
insert into sf1 values('12/2021','DIS',218190);
insert into sf1 values('12/2021','ADBE',41100);
insert into sf1 values('12/2021','AAPL',1239450);
insert into sf1 values('03/2022','AAPL',972780);
insert into sf1 values('03/2022','DIS',192490);
insert into sf1 values('03/2022','ADBE',42620);
insert into sf1 values('06/2022','ADBE',43860);
insert into sf1 values('06/2022','AAPL',829590);
insert into sf1 values('06/2022','DIS',215040);
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
select to_date(date1,'mm/yyyy') from sf1;
| to_date |
|---|
| 2021-12-01 |
| 2021-12-01 |
| 2021-12-01 |
| 2022-03-01 |
| 2022-03-01 |
| 2022-03-01 |
| 2022-06-01 |
| 2022-06-01 |
| 2022-06-01 |
SELECT 9
SELECT *
FROM crosstab('select ticker, to_date(date1,''mm/yyyy'') date1,revenue from sf1 order by 1 desc,2 ')
AS ct("ticker/date" text,"12/2021" integer,"03/2022" integer,"06/2022" integer);
| ticker/date | 12/2021 | 03/2022 | 06/2022 |
|---|---|---|---|
| DIS | 218190 | 192490 | 215040 |
| ADBE | 41100 | 42620 | 43860 |
| AAPL | 1239450 | 972780 | 829590 |
SELECT 3