How to join generate_series and an array

Question

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?

Answer 1

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.