Search code examples
sqlpostgresqlcross-joingenerate-seriesset-returning-functions

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

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?

Solution

  • 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: