Postgres 9.4

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

-- /** 
-- begin test data 
--  */
DROP TABLE IF EXISTS tmpSales ;
CREATE TEMP TABLE tmpSales AS
SELECT
  sale_id
  ,TRUNC(RANDOM()*12)+1 AS book_id
  ,TRUNC(RANDOM()*100)+1 AS customer_id
  ,(date '2010-01-01' + random() * (timestamp '2016-12-31' - timestamp '2010-01-01'))::DATE AS sale_date
FROM generate_series(1,1000) AS sale_id;
 

DROP TABLE IF EXISTS tmp_month_total ; 
CREATE TEMP TABLE tmp_month_total AS 
  SELECT
     date_part( 'year' , sale_date ) AS year
    ,date_part( 'month', sale_date ) AS mn 
    ,to_char(sale_date, 'mon') AS month
    ,COUNT(*) AS total
  FROM tmpSales
  GROUP BY date_part('year', sale_date), to_char(sale_date, 'mon') ,date_part( 'month', sale_date ) 
;

SELECT * FROM tmp_month_total LIMIT 15 ;
 


year
mn
month
total


2016
4
apr
14

2014
6
jun
15

2010
4
apr
8

2011
6
jun
7

2012
11
nov
12

2012
8
aug
15

2013
4
apr
12

2014
2
feb
8

2012
5
may
12

2011
7
jul
8

2014
7
jul
14

2011
2
feb
13

2012
3
mar
13

2015
1
jan
9

2013
8
aug
15



SELECT 15


-- /** 
-- using an anon. proc. to define the column headers
-- of a temporary record-type (as temp table)
-- based on existing data
--  **/
DROP TABLE IF EXISTS tmpTblTyp CASCADE ; 
DO LANGUAGE plpgsql $$ DECLARE v_sqlstring VARCHAR  = ''; BEGIN 
v_sqlstring := CONCAT( 'CREATE TEMP TABLE tmpTblTyp AS SELECT '   
                       ,(SELECT  STRING_AGG( CONCAT('NULL::int AS ' , month )::TEXT , ' ,' 
                            ORDER BY mn                        
                           )::TEXT
                                  || ' , NULL::int AS undec ' -- added these to show flexibility of json. 
                                  || ' , NULL::int AS dodec ' -- the actual data lacks these two extra columns
 
                           FROM
                           (SELECT DISTINCT month, mn FROM tmp_month_total )a )
                       ,' LIMIT 0 '    
                       ) ; -- RAISE NOTICE '%', v_sqlstring ;  
EXECUTE( v_sqlstring ) ; END $$; 

-- show our empty table struct
SELECT * FROM tmpTblTyp ; 
 


jan
feb
mar
apr
may
jun
jul
aug
sep
oct
nov
dec
undec
dodec



SELECT 0


-- /**
-- build the JSON record-set using the dyn. rec-type
--**/
DROP TABLE IF EXISTS tmpMoToJson ;
CREATE TEMP TABLE tmpMoToJson AS
	  SELECT 
	     year AS year
	     ,json_populate_recordset ( NULL::tmpTblTyp /** use temp table (above) as a record type definition!! **/
	                              , json_build_array( months ) 
	                              ) jprs /** builds row-type column that can be expanded with " (jprs).* "    **/   
FROM ( SELECT year
      -- accum & pivot data into JSON array
	  ,json_object_agg(month,total) AS months 
	   FROM tmp_month_total
	   GROUP BY year
	   ORDER BY year
	 ) a
; 



SELECT 7


SELECT  
year
,(jprs).* -- expand rowtype into columns
FROM tmpMoToJson ;


year
jan
feb
mar
apr
may
jun
jul
aug
sep
oct
nov
dec
undec
dodec


2010
9
14
15
8
11
7
14
9
9
16
18
14
null
null

2011
13
13
13
9
9
7
8
10
16
13
4
15
null
null

2012
11
9
13
8
12
13
18
15
12
9
12
10
null
null

2013
18
15
14
12
11
17
14
15
9
14
17
11
null
null

2014
9
8
15
8
10
15
14
15
14
11
8
15
null
null

2015
9
11
14
12
12
9
9
15
8
12
15
9
null
null

2016
13
10
14
14
10
14
13
5
13
15
8
11
null
null



SELECT 7

year	mn	month	total
2016	4	apr	14
2014	6	jun	15
2010	4	apr	8
2011	6	jun	7
2012	11	nov	12
2012	8	aug	15
2013	4	apr	12
2014	2	feb	8
2012	5	may	12
2011	7	jul	8
2014	7	jul	14
2011	2	feb	13
2012	3	mar	13
2015	1	jan	9
2013	8	aug	15

year	jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec	undec	dodec
2010	9	14	15	8	11	7	14	9	9	16	18	14	null	null
2011	13	13	13	9	9	7	8	10	16	13	4	15	null	null
2012	11	9	13	8	12	13	18	15	12	9	12	10	null	null
2013	18	15	14	12	11	17	14	15	9	14	17	11	null	null
2014	9	8	15	8	10	15	14	15	14	11	8	15	null	null
2015	9	11	14	12	12	9	9	15	8	12	15	9	null	null
2016	13	10	14	14	10	14	13	5	13	15	8	11	null	null