Postgres 16

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;


datetime
rnk
weekday
start_on_mon
see_it_s_mon
start_on_sun


2023-02-28 10:00:00
1
Tue
2023-02-27 00:00:00
Mon
2023-02-26 00:00:00

2023-02-28 10:00:00
1
Tue
2023-02-27 00:00:00
Mon
2023-02-26 00:00:00

2023-03-01 10:00:00
2
Wed
2023-02-27 00:00:00
Mon
2023-02-26 00:00:00

2023-03-01 10:00:00
2
Wed
2023-02-27 00:00:00
Mon
2023-02-26 00:00:00

2023-03-01 10:00:00
2
Wed
2023-02-27 00:00:00
Mon
2023-02-26 00:00:00

2023-03-03 10:00:00
3
Fri
2023-02-27 00:00:00
Mon
2023-02-26 00:00:00

2023-03-04 10:00:00
4
Sat
2023-02-27 00:00:00
Mon
2023-02-26 00:00:00

2023-03-05 10:00:00
5
Sun
2023-02-27 00:00:00
Mon
2023-03-05 00:00:00

2023-03-06 10:00:00
6
Mon
2023-03-06 00:00:00
Mon
2023-03-05 00:00:00

2023-03-07 10:00:00
7
Tue
2023-03-06 00:00:00
Mon
2023-03-05 00:00:00

2023-03-08 10:00:00
8
Wed
2023-03-06 00:00:00
Mon
2023-03-05 00:00:00

2023-03-09 10:00:00
9
Thu
2023-03-06 00:00:00
Mon
2023-03-05 00:00:00

2023-03-31 10:00:00
10
Fri
2023-03-27 00:00:00
Mon
2023-03-26 00:00:00



SELECT 13


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


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


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

datetime	rnk	weekday	start_on_mon	see_it_s_mon	start_on_sun
2023-02-28 10:00:00	1	Tue	2023-02-27 00:00:00	Mon	2023-02-26 00:00:00
2023-02-28 10:00:00	1	Tue	2023-02-27 00:00:00	Mon	2023-02-26 00:00:00
2023-03-01 10:00:00	2	Wed	2023-02-27 00:00:00	Mon	2023-02-26 00:00:00
2023-03-01 10:00:00	2	Wed	2023-02-27 00:00:00	Mon	2023-02-26 00:00:00
2023-03-01 10:00:00	2	Wed	2023-02-27 00:00:00	Mon	2023-02-26 00:00:00
2023-03-03 10:00:00	3	Fri	2023-02-27 00:00:00	Mon	2023-02-26 00:00:00
2023-03-04 10:00:00	4	Sat	2023-02-27 00:00:00	Mon	2023-02-26 00:00:00
2023-03-05 10:00:00	5	Sun	2023-02-27 00:00:00	Mon	2023-03-05 00:00:00
2023-03-06 10:00:00	6	Mon	2023-03-06 00:00:00	Mon	2023-03-05 00:00:00
2023-03-07 10:00:00	7	Tue	2023-03-06 00:00:00	Mon	2023-03-05 00:00:00
2023-03-08 10:00:00	8	Wed	2023-03-06 00:00:00	Mon	2023-03-05 00:00:00
2023-03-09 10:00:00	9	Thu	2023-03-06 00:00:00	Mon	2023-03-05 00:00:00
2023-03-31 10:00:00	10	Fri	2023-03-27 00:00:00	Mon	2023-03-26 00:00:00