How to use generate_series() to generate a grid of values

Question

I would like to generate a grid of (row,column) pairs, like:

1 | 1
1 | 2
1 | 3
...
2 | 1
2 | 2
...

My naive approach has this puzzling behaviour:

select generate_series(1,5), generate_series(1, 5);
 generate_series | generate_series
-----------------+-----------------
               1 |               1
               2 |               2
               3 |               3
               4 |               4
               5 |               5
(5 rows)

select generate_series(1,5), generate_series(1, 4);
 generate_series | generate_series
-----------------+-----------------
               1 |               1
               2 |               2
               3 |               3
               4 |               4
               5 |               1
               1 |               2
               2 |               3
               3 |               4
               4 |               1
               5 |               2
               1 |               3
               2 |               4
               3 |               1
               4 |               2
               5 |               3
               1 |               4
               2 |               1
               3 |               2
               4 |               3
               5 |               4
(20 rows)

It seems to repeat each series until a row is reached that has the final value of each series.

What's the right way to use this function as a sort of cross-join with itself?

Answer 1

Move the function calls to the FROM clause:

SELECT *
FROM   generate_series(1,5) a
     , generate_series(1,5) b;

Or upgrade to Postgres 10 or later, where this odd behavior was finally changed. Detailed explanation:

• What is the expected behaviour for multiple set-returning functions in SELECT clause?

Comma-separated FROM items are cross-joined. See:

• Why does this implicit join get planned differently than an explicit join?
• What does [FROM x, y] mean in Postgres?