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?

I don't see the array you mentioned in your query. And please always disclose your version of Postgres. — Erwin Brandstetter
– Erwin Brandstetter, Commented Aug 14, 2019 at 21:54

Erwin Brandstetter · Accepted Answer · 2019-08-14 21:51:18Z

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.

answered Aug 14, 2019 at 21:51

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to join generate_series and an array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related