Postgres 12

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 book(
  id    int PRIMARY KEY
, name  text NOT NULL
, sid   int NOT NULL
, count int NOT NULL
, date  date NOT NULL
, UNIQUE(sid, date, name)
);

INSERT INTO book VALUES 
  (1,  'XYZ', 1, 20, '2015-11-11')
, (2,  'JHG', 1, 10, '2015-11-11')
, (3,  'UYH', 1, 10, '2015-11-15')
, (4,  'ZRE', 1, 50, '2015-11-17')
, (5,  'ZZ4', 1, 54, '2015-11-10')
, (6,  'ZZ5', 1, 55, '2015-11-09')
, (7,  'ZZ6', 1, 56, '2015-11-08')
, (8,  'ZZ7', 1, 57, '2015-11-07')
, (9,  'ZZ8', 1, 58, '2015-11-06')
, (10, 'ZZ9', 1, 59, '2015-11-05')
, (11, 'ZZZ', 1, 60, '2015-11-04')
, (12, 'XXX', 1, 60, '2015-11-01')
, (13, 'JHG', 2, 99, '2015-11-11');  -- more books in other store

CREATE TABLE



INSERT 0 13


-- last 10 days:
SELECT DISTINCT date
FROM   book
WHERE  sid = 1
ORDER  BY date DESC
LIMIT  10;


date


2015-11-17

2015-11-15

2015-11-11

2015-11-10

2015-11-09

2015-11-08

2015-11-07

2015-11-06

2015-11-05

2015-11-04



SELECT 10


-- numbers for last 10 days:
SELECT *
FROM  (
   SELECT name
        , dense_rank() OVER (ORDER BY date DESC) AS date_rnk
        , count
   FROM   book
   WHERE  sid = 1
   ) sub
WHERE  date_rnk < 11
ORDER  BY name, date_rnk DESC;


name
date_rnk
count


JHG
3
10

UYH
2
10

XYZ
3
20

ZRE
1
50

ZZ4
4
54

ZZ5
5
55

ZZ6
6
56

ZZ7
7
57

ZZ8
8
58

ZZ9
9
59

ZZZ
10
60



SELECT 11


-- column names for output columns
SELECT 'bookname, "' || string_agg(to_char(date, 'DD/MM/YYYY'), '", "' ORDER BY date) || '"'
FROM  (
   SELECT DISTINCT date
   FROM   book
   WHERE  sid = 1
   ORDER  BY date DESC
   LIMIT  10
   ) sub;


?column?


bookname, "04/11/2015", "05/11/2015", "06/11/2015", "07/11/2015", "08/11/2015", "09/11/2015", "10/11/2015", "11/11/2015", "15/11/2015", "17/11/2015"



SELECT 1


-- simple crostab function with static column names
SELECT * FROM crosstab(
  'SELECT *
   FROM  (
      SELECT name
           , dense_rank() OVER (ORDER BY date DESC) AS date_rnk
           , count
      FROM   book
      WHERE  sid = 1
      ) sub
   WHERE  date_rnk < 11
   ORDER  BY name, date_rnk DESC'
, 'SELECT generate_series(10, 1, -1)'
 ) AS (bookname text
     , date1 int, date2 int, date3 int, date4 int, date5 int
     , date6 int, date7 int, date8 int, date9 int, date10 int);


bookname
date1
date2
date3
date4
date5
date6
date7
date8
date9
date10


JHG
null
null
null
null
null
null
null
10
null
null

UYH
null
null
null
null
null
null
null
null
10
null

XYZ
null
null
null
null
null
null
null
20
null
null

ZRE
null
null
null
null
null
null
null
null
null
50

ZZ4
null
null
null
null
null
null
54
null
null
null

ZZ5
null
null
null
null
null
55
null
null
null
null

ZZ6
null
null
null
null
56
null
null
null
null
null

ZZ7
null
null
null
57
null
null
null
null
null
null

ZZ8
null
null
58
null
null
null
null
null
null
null

ZZ9
null
59
null
null
null
null
null
null
null
null

ZZZ
60
null
null
null
null
null
null
null
null
null



SELECT 11


/* -- insufficient privileges on dbfiddle.uk
CREATE OR REPLACE FUNCTION pg_temp.crosstab_int10(text, text)
  RETURNS TABLE (bookname text, date1 int, date2 int, date3 int, date4 int, date5 int
                              , date6 int, date7 int, date8 int, date9 int, date10 int)
  LANGUAGE C STABLE STRICT AS
 '$libdir/tablefunc','crosstab_hash';
*/
-- generate query
CREATE OR REPLACE FUNCTION f_generate_date10_sql(_sid int = 1) 
  RETURNS text
  LANGUAGE sql AS
$func$
SELECT format(
 $$SELECT * FROM crosstab_int10(
  'SELECT *
   FROM  (
      SELECT name
           , dense_rank() OVER (ORDER BY date DESC) AS date_rnk
           , count
      FROM   book
      WHERE  sid = %1$s
      ) sub
   WHERE  date_rnk < 11
   ORDER  BY name, date_rnk DESC'
, 'SELECT generate_series(10, 1, -1)'
   ) AS ct(bookname, "$$
|| string_agg(to_char(date, 'DD/MM/YYYY'), '", "' ORDER BY date) || '")'
 , _sid)
FROM  (
   SELECT DISTINCT date
   FROM   book
   WHERE  sid = 1
   ORDER  BY date DESC
   LIMIT  10
   ) sub
$func$;

CREATE FUNCTION


-- Generate query for sid = 1
SELECT f_generate_date10_sql(1);


f_generate_date10_sql


SELECT * FROM crosstab_int10(
  'SELECT *
   FROM  (
      SELECT name
           , dense_rank() OVER (ORDER BY date DESC) AS date_rnk
           , count
      FROM   book
      WHERE  sid = 1
      ) sub
   WHERE  date_rnk < 11
   ORDER  BY name, date_rnk DESC'
, 'SELECT generate_series(10, 1, -1)'
   ) AS ct(bookname, "04/11/2015", "05/11/2015", "06/11/2015", "07/11/2015", "08/11/2015", "09/11/2015", "10/11/2015", "11/11/2015", "15/11/2015", "17/11/2015")



SELECT 1

bookname	date1	date2	date3	date4	date5	date6	date7	date8	date9	date10
JHG	null	null	null	null	null	null	null	10	null	null
UYH	null	null	null	null	null	null	null	null	10	null
XYZ	null	null	null	null	null	null	null	20	null	null
ZRE	null	null	null	null	null	null	null	null	null	50
ZZ4	null	null	null	null	null	null	54	null	null	null
ZZ5	null	null	null	null	null	55	null	null	null	null
ZZ6	null	null	null	null	56	null	null	null	null	null
ZZ7	null	null	null	57	null	null	null	null	null	null
ZZ8	null	null	58	null	null	null	null	null	null	null
ZZ9	null	59	null	null	null	null	null	null	null	null
ZZZ	60	null	null	null	null	null	null	null	null	null

name	date_rnk	count
JHG	3	10
UYH	2	10
XYZ	3	20
ZRE	1	50
ZZ4	4	54
ZZ5	5	55
ZZ6	6	56
ZZ7	7	57
ZZ8	8	58
ZZ9	9	59
ZZZ	10	60

name	date_rnk	count
JHG	3	10
UYH	2	10
XYZ	3	20
ZRE	1	50
ZZ4	4	54
ZZ5	5	55
ZZ6	6	56
ZZ7	7	57
ZZ8	8	58
ZZ9	9	59
ZZZ	10	60

name	date_rnk	count
JHG	3	10
UYH	2	10
XYZ	3	20
ZRE	1	50
ZZ4	4	54
ZZ5	5	55
ZZ6	6	56
ZZ7	7	57
ZZ8	8	58
ZZ9	9	59
ZZZ	10	60