I want to generate a list of columns using generate_series()
and joining it to a specific array (a list of value, e.g. [1, 2, 3]
), and my expected result looks like this:
Day | Code 2019-08-01 | 1 2019-08-02 | 1 2019-08-03 | 1 2019-08-01 | 2 2019-08-02 | 2 2019-08-03 | 2 2019-08-01 | 3 2019-08-02 | 3 2019-08-03 | 3
This query:
SELECT generate_series(timestamp '2019-08-01' , timestamp '2019-08-03' , interval '1 day') AS DAY, 1 AS CODE;
will give me the result:
Day | Code 2019-08-01 | 1 2019-08-02 | 1 2019-08-03 | 1
Then how to update the query to get the expected result?
Advertisement
Answer
This is one way of many to produce your desired result exactly:
SELECT day::date, code FROM generate_series(timestamp '2019-08-01' , timestamp '2019-08-03' , interval '1 day') day CROSS JOIN unnest ('{1,2,3}'::int[]) code; -- using an actual array here
The point being: you need a CROSS JOIN
to produce a Cartesian Product.