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

select d::date, row_number() over (order by d), extract('dow' from d)
from generate_series(current_date+ 1, current_date + 4 * 2 + 5, '1d') d


d
row_number
date_part


2023-05-25
1
4

2023-05-26
2
5

2023-05-27
3
6

2023-05-28
4
0

2023-05-29
5
1

2023-05-30
6
2

2023-05-31
7
3

2023-06-01
8
4

2023-06-02
9
5

2023-06-03
10
6

2023-06-04
11
0

2023-06-05
12
1

2023-06-06
13
2



SELECT 13


-- query with positive num_days (4)

select 
    d::date, 
    CASE 
        WHEN 4 >= 0 THEN row_number() over (order by d) - 1
        ELSE row_number() over (order by d DESC) * -1 + 1
    END as row_number,
    extract('dow' from d)
from
    generate_series(
        CASE WHEN 4 >= 0 THEN current_date ELSE current_date + 4 * 2 - 5 END, 
        CASE WHEN 4 >= 0 THEN current_date + 4 * 2 + 5 ELSE current_date END, 
        '1d'
    ) d


d
row_number
date_part


2023-05-24
0
3

2023-05-25
1
4

2023-05-26
2
5

2023-05-27
3
6

2023-05-28
4
0

2023-05-29
5
1

2023-05-30
6
2

2023-05-31
7
3

2023-06-01
8
4

2023-06-02
9
5

2023-06-03
10
6

2023-06-04
11
0

2023-06-05
12
1

2023-06-06
13
2



SELECT 14


-- query with negative num_days (-4)

select 
    d::date, 
    CASE 
        WHEN -4 >= 0 THEN row_number() over (order by d) - 1
        ELSE row_number() over (order by d DESC) * -1 + 1
    END as row_number,
    extract('dow' from d)
from
    generate_series(
        CASE WHEN -4 >= 0 THEN current_date ELSE current_date + -4 * 2 - 5 END, 
        CASE WHEN -4 >= 0 THEN current_date + -4 * 2 + 5 ELSE current_date END, 
        '1d'
    ) d
   


d
row_number
date_part


2023-05-24
0
3

2023-05-23
-1
2

2023-05-22
-2
1

2023-05-21
-3
0

2023-05-20
-4
6

2023-05-19
-5
5

2023-05-18
-6
4

2023-05-17
-7
3

2023-05-16
-8
2

2023-05-15
-9
1

2023-05-14
-10
0

2023-05-13
-11
6

2023-05-12
-12
5

2023-05-11
-13
4



SELECT 14

d	row_number	date_part
2023-05-25	1	4
2023-05-26	2	5
2023-05-27	3	6
2023-05-28	4	0
2023-05-29	5	1
2023-05-30	6	2
2023-05-31	7	3
2023-06-01	8	4
2023-06-02	9	5
2023-06-03	10	6
2023-06-04	11	0
2023-06-05	12	1
2023-06-06	13	2

d	row_number	date_part
2023-05-24	0	3
2023-05-25	1	4
2023-05-26	2	5
2023-05-27	3	6
2023-05-28	4	0
2023-05-29	5	1
2023-05-30	6	2
2023-05-31	7	3
2023-06-01	8	4
2023-06-02	9	5
2023-06-03	10	6
2023-06-04	11	0
2023-06-05	12	1
2023-06-06	13	2

d	row_number	date_part
2023-05-24	0	3
2023-05-23	-1	2
2023-05-22	-2	1
2023-05-21	-3	0
2023-05-20	-4	6
2023-05-19	-5	5
2023-05-18	-6	4
2023-05-17	-7	3
2023-05-16	-8	2
2023-05-15	-9	1
2023-05-14	-10	0
2023-05-13	-11	6
2023-05-12	-12	5
2023-05-11	-13	4