You can do this in a couple of ways (all the code below is available on the fiddle here).
Solution using GENERATE_SERIES:
For starters, I inserted a few more records to make the use case a bit more realistic I also added a couple of reasonable constraints to the table.
CREATE TABLE test
(
id SMALLINT NOT NULL PRIMARY KEY, -- PK added
name TEXT NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
CONSTRAINT test_name_uq UNIQUE (name), -- maybe, maybe not?
CONSTRAINT sd_lt_ed_ck CHECK (start_date < end_date) -- important!
);
Populate it:
INSERT INTO test
VALUES
(1, 'Rahul', '2021-06-01', '2021-06-06'),
(2, 'Bill' , '2021-06-02', '2021-06-10'),
(3, 'Mary' , '2021-07-15', '2021-07-22'),
(4, 'Fred' , '2021-07-20', '2021-07-27'),
(5, 'Joe' , '2021-08-01', '2021-08-04'),
(6, 'Jim ' , '2021-09-04', '2021-09-05'),
(7, 'John' , '2021-09-17', '2021-09-21');
and then I ran the following query:
SELECT
id,
GENERATE_SERIES
(t.start_date, t.end_date, '1 DAY')::DATE AS sd,
t.end_date
FROM test t
ORDER BY t.id, t.start_date;
Result (snipped for brevity - see fiddle):
id sd end_date
1 2021-06-01 2021-06-06
1 2021-06-02 2021-06-06
1 2021-06-03 2021-06-06
1 2021-06-04 2021-06-06
1 2021-06-05 2021-06-06
1 2021-06-06 2021-06-06
2 2021-06-02 2021-06-10
2 2021-06-03 2021-06-10
...
... more records
...
an "edge case" of just two consecutive days also works nicely:
6 2021-09-04 2021-09-05
6 2021-09-05 2021-09-05
I did a performance analysis of my solution versus the other one offered by @Akina and while it appears that my solution has more operations, it is consistently faster (typically ~ 2/3 of the time - although occasionally, mine is slower) than his. See the fiddle here.
Now, it is impossible to properly benchmark a solution when we're only looking at 7 records on a server over which we have no control - what's happening elsewhere on the machine? I would urge you to test any chosen solution using your own (test) system(s) to clarify this to your own satisfaction.
Solution using a RECURSIVE CTE (RCTE):
Another interesting solution also exists using an RCTE as follows:
WITH RECURSIVE cte (n, id, sd, ed) AS
(
SELECT 1, t.id, t.start_date, t.end_date
FROM test t
UNION ALL
SELECT n+1, c.id, (c.sd + INTERVAL '1 DAY')::DATE, c.ed
FROM cte c
WHERE c.sd < (SELECT z.end_date FROM test z WHERE z.id = c.id)
)
SELECT * FROM cte c2
ORDER BY c2.id, c2.sd, c2.ed;
Result:
Same as for the query above - including the two consecutive days
Performance analysis:
I've included the output of EXPLAIN (ANALYZE, BUFFERS) for both queries in the fiddle. As you can see, the GENERATE_SERIES query takes about 50% of the time of the RCTE. So why, you might ask, would one bother with the RCTE?
Well, they are very powerful and they allow the programmer to add complex logic to their queries - I would urge you to explore them - maybe not for this case, but they are worth bearing in mind for future scenarios.
p.s. welcome to dba.se. For future reference, please do not post the same question both here and on StackOverflow.
Database questions are very much on-topic here. If you've asked a question and don't receive a response within a reasonable amount of time, feel free to ask elsewhere, but, in that case, please put a link from the old question to the new to avoid duplication of effort!
