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
16

2014
6
jun
14

2010
4
apr
17

2011
6
jun
11

2012
11
nov
9

2012
8
aug
18

2013
4
apr
12

2012
5
may
16

2014
2
feb
10

2011
7
jul
5

2012
3
mar
13

2014
7
jul
9

2011
2
feb
11

2015
1
jan
19

2012
4
apr
14



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
12
11
16
17
12
10
15
15
9
8
15
14
null
null

2011
9
11
9
7
10
11
5
6
9
13
14
20
null
null

2012
11
6
13
14
16
14
14
18
9
7
9
10
null
null

2013
19
6
16
12
17
16
10
11
9
16
10
13
null
null

2014
7
10
12
14
7
14
9
8
15
11
8
13
null
null

2015
19
14
11
12
6
11
11
16
9
12
14
14
null
null

2016
12
12
6
16
9
14
18
16
13
12
14
7
null
null



SELECT 7

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

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