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 room_categories 
(
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(25)
);


CREATE TABLE category_prices 
(
    category_price_id SERIAL PRIMARY KEY,
    room_category_id INTEGER REFERENCES room_categories(category_id),
    valid_period daterange,
    base_price DECIMAL(10, 2)
);

CREATE TABLE hotel_bookings 
(
    booking_id SERIAL PRIMARY KEY,
    guest_name VARCHAR(255),
    room_category_id INTEGER REFERENCES room_categories(category_id),
    booking_period daterange
);
--the data:

INSERT INTO room_categories (category_name) VALUES
    ('single room'),
    ('double room')
returning *;

INSERT INTO category_prices (room_category_id, valid_period, base_price) VALUES
    (1, '[2023-01-01, 2023-01-31)', 80.00),
    (1, '[2023-02-01, 2023-02-28)', 85.00),
    (1, '[2023-03-01, 2023-03-31)', 88.00),
    (2, '[2023-01-01, 2023-01-31)', 100.00),
    (2, '[2023-02-01, 2023-02-28)', 105.00),
    (2, '[2023-03-01, 2023-03-31)', 108.00)
returning *;

INSERT INTO hotel_bookings (guest_name, room_category_id, booking_period) VALUES
    ('John Doe', 1, '[2023-01-15, 2023-01-20)'), --correct calced, 1 intersection
    ('Jane Smith', 1, '[2023-01-30, 2023-02-02)'), -- 2 shares (need/want a sumup)
    ('Jane Smith', 1, '[2023-02-25, 2023-03-03)'),
    ('Jordan Miller', 2, '[2023-01-30, 2023-03-02)')-- 3 shares (need/want a sumup)
returning *;

CREATE TABLE



CREATE TABLE



CREATE TABLE




category_id
category_name


1
single room

2
double room



INSERT 0 2




category_price_id
room_category_id
valid_period
base_price


1
1
[2023-01-01,2023-01-31)
80.00

2
1
[2023-02-01,2023-02-28)
85.00

3
1
[2023-03-01,2023-03-31)
88.00

4
2
[2023-01-01,2023-01-31)
100.00

5
2
[2023-02-01,2023-02-28)
105.00

6
2
[2023-03-01,2023-03-31)
108.00



INSERT 0 6




booking_id
guest_name
room_category_id
booking_period


1
John Doe
1
[2023-01-15,2023-01-20)

2
Jane Smith
1
[2023-01-30,2023-02-02)

3
Jane Smith
1
[2023-02-25,2023-03-03)

4
Jordan Miller
2
[2023-01-30,2023-03-02)



INSERT 0 4


SELECT
    hb.booking_id,
    hb.guest_name,
    hb.room_category_id,
    hb.booking_period,
    cp.base_price,
    SUM(CASE WHEN  upper(hb.booking_period * cp.valid_period) = upper(hb.booking_period) 
             THEN (upper(hb.booking_period * cp.valid_period) - lower(hb.booking_period * cp.valid_period)) * cp.base_price
             ELSE (upper(hb.booking_period * cp.valid_period) - lower(hb.booking_period * cp.valid_period) + 1) * cp.base_price
        END )
FROM hotel_bookings hb
JOIN category_prices cp 
ON hb.room_category_id = cp.room_category_id 
AND hb.booking_period && cp.valid_period
GROUP BY hb.booking_id, 
  hb.guest_name, 
  hb.room_category_id, 
  hb.booking_period, 
  cp.base_price, 
  cp.valid_period;


booking_id
guest_name
room_category_id
booking_period
base_price
sum


1
John Doe
1
[2023-01-15,2023-01-20)
80.00
400.00

2
Jane Smith
1
[2023-01-30,2023-02-02)
80.00
160.00

2
Jane Smith
1
[2023-01-30,2023-02-02)
85.00
85.00

3
Jane Smith
1
[2023-02-25,2023-03-03)
85.00
340.00

3
Jane Smith
1
[2023-02-25,2023-03-03)
88.00
176.00

4
Jordan Miller
2
[2023-01-30,2023-03-02)
100.00
200.00

4
Jordan Miller
2
[2023-01-30,2023-03-02)
105.00
2940.00

4
Jordan Miller
2
[2023-01-30,2023-03-02)
108.00
108.00



SELECT 8


SELECT
    hb.booking_id,
    hb.guest_name,
    hb.room_category_id,
    hb.booking_period,
    jsonb_pretty(jsonb_object_agg(cp.valid_period,cp.base_price)),
    SUM(CASE WHEN  upper(hb.booking_period * cp.valid_period) = upper(hb.booking_period) 
             THEN (upper(hb.booking_period * cp.valid_period) - lower(hb.booking_period * cp.valid_period)) * cp.base_price
             ELSE (upper(hb.booking_period * cp.valid_period) - lower(hb.booking_period * cp.valid_period) + 1) * cp.base_price
        END )
FROM hotel_bookings hb
JOIN category_prices cp 
ON hb.room_category_id = cp.room_category_id 
AND hb.booking_period && cp.valid_period
GROUP BY hb.booking_id, 
  hb.guest_name, 
  hb.room_category_id, 
  hb.booking_period;


booking_id
guest_name
room_category_id
booking_period
jsonb_pretty
sum


1
John Doe
1
[2023-01-15,2023-01-20)
{
    "[2023-01-01,2023-01-31)": 80.00
}
400.00

2
Jane Smith
1
[2023-01-30,2023-02-02)
{
    "[2023-01-01,2023-01-31)": 80.00,
    "[2023-02-01,2023-02-28)": 85.00
}
245.00

3
Jane Smith
1
[2023-02-25,2023-03-03)
{
    "[2023-02-01,2023-02-28)": 85.00,
    "[2023-03-01,2023-03-31)": 88.00
}
516.00

4
Jordan Miller
2
[2023-01-30,2023-03-02)
{
    "[2023-01-01,2023-01-31)": 100.00,
    "[2023-02-01,2023-02-28)": 105.00,
    "[2023-03-01,2023-03-31)": 108.00
}
3248.00



SELECT 4

category_price_id	room_category_id	valid_period	base_price
1	1	[2023-01-01,2023-01-31)	80.00
2	1	[2023-02-01,2023-02-28)	85.00
3	1	[2023-03-01,2023-03-31)	88.00
4	2	[2023-01-01,2023-01-31)	100.00
5	2	[2023-02-01,2023-02-28)	105.00
6	2	[2023-03-01,2023-03-31)	108.00

booking_id	guest_name	room_category_id	booking_period
1	John Doe	1	[2023-01-15,2023-01-20)
2	Jane Smith	1	[2023-01-30,2023-02-02)
3	Jane Smith	1	[2023-02-25,2023-03-03)
4	Jordan Miller	2	[2023-01-30,2023-03-02)

booking_id	guest_name	room_category_id	booking_period	jsonb_pretty	sum
1	John Doe	1	[2023-01-15,2023-01-20)	{ "[2023-01-01,2023-01-31)": 80.00 }	400.00
2	Jane Smith	1	[2023-01-30,2023-02-02)	{ "[2023-01-01,2023-01-31)": 80.00, "[2023-02-01,2023-02-28)": 85.00 }	245.00
3	Jane Smith	1	[2023-02-25,2023-03-03)	{ "[2023-02-01,2023-02-28)": 85.00, "[2023-03-01,2023-03-31)": 88.00 }	516.00
4	Jordan Miller	2	[2023-01-30,2023-03-02)	{ "[2023-01-01,2023-01-31)": 100.00, "[2023-02-01,2023-02-28)": 105.00, "[2023-03-01,2023-03-31)": 108.00 }	3248.00