Postgres 14

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 invoices (id serial primary key, year integer, creation_date date, marking text) ;

insert into invoices (year, creation_date, marking) values
(2020, '20200101', 'Delivered'),
(2020, '20200101', 'Not Delivered'),
(2020, '20200101', 'Not Received'),
(2020, '20200301', 'Delivered'),
(2020, '20200301', 'Delivered'),
(2020, '20200301', 'Not Delivered'),
(2020, '20200301', 'Not Delivered'),
(2021, '20210301', 'Delivered'),
(2021, '20210101', 'Delivered'),
(2021, '20210101', 'Not Received'),
(2021, '20210101', 'Not Delivered'),
(2021, '20210101', 'Not Delivered'),
(2021, '20210201', 'Not Received'),
(2021, '20210801', 'Delivered'),
(2021, '20210801', 'Not Delivered'),
(2021, '20210801', 'Not Received'),
(2021, '20210801', 'Not Received'),
(2022, '20220801', 'Delivered'),
(2022, '20220801', 'Delivered'),
(2022, '20221101', 'Not Delivered'),
(2022, '20221101', 'Delivered'),
(2022, '20221101', 'Delivered'),
(2022, '20221101', 'Not Delivered'),
(2022, '20221101', 'Not Delivered'),
(2022, '20221101', 'Not Received'),
(2022, '20221201', 'Not Received'),
(2022, '20221201', 'Not Received');

CREATE TABLE



INSERT 0 27


    CREATE OR REPLACE PROCEDURE create_composite_type() LANGUAGE plpgsql AS $$
    DECLARE
      column_list text ;
    BEGIN
      SELECT string_agg(DISTINCT quote_ident(marking) || ' bigint', ',' ORDER BY quote_ident(marking) || ' bigint' ASC)
        INTO column_list
        FROM invoices ;
      
      EXECUTE 'DROP TYPE IF EXISTS composite_type' ;
      EXECUTE 'CREATE TYPE composite_type AS (' || column_list || ')' ;
    END ;
    $$ ;
    
    CALL create_composite_type() ;

CREATE PROCEDURE



CALL


SELECT a.year
     , TO_CHAR(a.year_month, 'Month') AS month
     , (jsonb_populate_record( null :: composite_type
                             , jsonb_object_agg(a.marking, a.count)
                             )
       ).*
 FROM
    ( SELECT year
           , date_trunc('month', creation_date) AS year_month
           , marking
           , count(*) AS count
        FROM invoices AS v
       GROUP BY 1,2,3
    ) AS a
GROUP BY 1,2
ORDER BY month


year
month
Delivered
Not Delivered
Not Received


2021
August
1
1
2

2022
August
2
null
null

2022
December
null
null
2

2021
February
null
null
1

2020
January
1
1
1

2021
January
1
2
1

2020
March
2
2
null

2021
March
1
null
null

2022
November
2
3
1



SELECT 9

year	month	Delivered	Not Delivered	Not Received
2021	August	1	1	2
2022	August	2	null	null
2022	December	null	null	2
2021	February	null	null	1
2020	January	1	1	1
2021	January	1	2	1
2020	March	2	2	null
2021	March	1	null	null
2022	November	2	3	1