4

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Steve Bennett
  • 114,604
  • 39
  • 168
  • 219

1 Answers1

6

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:

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228