Postgres 13

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 tbl(id int, datetime timestamp);
INSERT INTO tbl VALUES
  ( 1, '2023-02-28 10:00')
, ( 2, '2023-02-28 10:00')
, ( 3, '2023-03-01 10:00')
, ( 4, '2023-03-01 10:00')
, ( 5, '2023-03-01 10:00')
, ( 6, '2023-03-06 10:00')
, ( 7, '2023-03-05 10:00')
, ( 8, '2023-03-04 10:00')
, ( 9, '2023-03-03 10:00')
, (10, '2023-03-09 10:00')
, (11, '2023-03-07 10:00')
, (12, '2023-03-08 10:00')
, (13, '2023-03-31 10:00')
;

CREATE TABLE



INSERT 0 13


-- data
SELECT datetime
     , dense_rank() OVER (ORDER BY datetime) AS rnk
     , to_char(datetime, 'Dy') AS weekday
     , date_trunc('week', datetime) AS start_on_mon
     , to_char(date_trunc('week', datetime), 'Dy') AS see_it_s_mon
     , date_bin('7 days', datetime, timestamp '2023-1-1') AS start_on_sun
FROM   tbl;

ERROR:  function date_bin(unknown, timestamp without time zone, timestamp without time zone) does not exist
LINE 7:      , date_bin('7 days', datetime, timestamp '2023-1-1') AS...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.


-- date_bin() in Postgres 14+
SELECT date_bin('7 days', datetime, timestamp '2023-1-1') AS date_week
     , max(rnk) AS runing_total_id
FROM  (SELECT datetime, dense_rank() OVER (ORDER BY datetime) AS rnk FROM tbl) sub
GROUP  BY 1
ORDER  BY 1, 2;  -- optional

ERROR:  function date_bin(unknown, timestamp without time zone, timestamp without time zone) does not exist
LINE 2: SELECT date_bin('7 days', datetime, timestamp '2023-1-1') AS...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.


-- shift-trick with date_trunc() in Postgres 13-
SELECT date_trunc('week', datetime + interval '1 day') - interval '1 day' AS date_week
     , max(rnk) AS runing_total_id
FROM  (SELECT datetime, dense_rank() OVER (ORDER BY datetime) AS rnk FROM tbl) sub
GROUP  BY 1
ORDER  BY 1, 2;  -- optional


date_week
runing_total_id


2023-02-26 00:00:00
4

2023-03-05 00:00:00
9

2023-03-26 00:00:00
10



SELECT 3


SELECT to_char(timestamp '2023-1-1', 'Dy') AS must_be_sonday;


must_be_sonday


Sun



SELECT 1

date_week	runing_total_id
2023-02-26 00:00:00	4
2023-03-05 00:00:00	9
2023-03-26 00:00:00	10