CODEWITHSUNDEEP
postgresql
Ivan Kolyhalov
Ivan Kolyhalov

Reputation: 1002

How to generate date series with corresponding varchar column

Is it possible to generate date series with text column?

Say I need 'varchar 1', 'varchar 2', 'varchar 3' with generated dates like this:

varchars date
varchar 1 2020-02-01
varchar 2 2020-02-01
varchar 3 2020-02-01
varchar 1 2020-02-02
varchar 2 2020-02-02
varchar 3 2020-02-02
varchar 1 2020-02-03
varchar 2 2020-02-03
varchar 3 2020-02-03

How can I achieve this? What would be the right sql? I have managed to generate_series with dates, but have no clue how to do it with corresponding varchar column.

Thanks beforehands.

Upvotes: 1

Views: 174

Answers (1)

S-Man
S-Man

Reputation: 23766

demo:db<>fiddle

You can use a CROSS JOIN on the generate_series() function for that

SELECT
    varchars,
    my_date
FROM (
    VALUES ('varchar 1'), ('varchar 2'), ('varchar 3')
) as t(varchars)
CROSS JOIN generate_series(
    my_start_date, my_end_date, interval '1 day'
) as my_date

Upvotes: 1

Related Questions