sql - How to transform this data in PostgreSQL

Question

How do I transform this data

Date1	Date2	ID
2021-01-01	2021-01-04	1
2021-01-05	2021-01-25	2

Answer 1

You intuition is correct, and a calendar table approach should work here:

WITH dates AS (
    SELECT date_trunc('day', dt)::date AS dt
    FROM generate_series (
        '2021-01-01'::timestamp,
        '2021-01-31'::timestamp,
        '1 day'::interval) dt
)

SELECT d.dt AS Date, t.ID
FROM dates d
LEFT JOIN yourTable t
    ON d.dt BETWEEN t.Date1 AND t.Date2;

Demo