How to Use LATERAL with generate_series in PostgreSQL

In PostgreSQL, use LATERAL to join each row of a table with a set-returning function like generate_series. This allows you to generate multiple rows per original row dynamically, useful for expanding data or creating sequences related to each row.

Syntax

The basic syntax to use LATERAL with generate_series is:

SELECT columns, gs.value
FROM table_name
CROSS JOIN LATERAL generate_series(start, stop, step) AS gs(value);

Here:

• table_name is your original table.
• generate_series(start, stop, step) generates a series of numbers.
• LATERAL allows generate_series to access columns from table_name.
• gs(value) names the output column from generate_series.

sql

SELECT t.id, gs.num
FROM my_table t
CROSS JOIN LATERAL generate_series(1, 3) AS gs(num);

Example

This example shows how to use LATERAL with generate_series to create multiple rows per original row. For each row in my_table, it generates numbers from 1 to 3.

sql

CREATE TEMP TABLE my_table (id INT, name TEXT);
INSERT INTO my_table VALUES (1, 'Alice'), (2, 'Bob');

SELECT t.id, t.name, gs.num
FROM my_table t
CROSS JOIN LATERAL generate_series(1, 3) AS gs(num)
ORDER BY t.id, gs.num;

Output

 id | name  | num 
----+-------+-----
  1 | Alice |   1
  1 | Alice |   2
  1 | Alice |   3
  2 | Bob   |   1
  2 | Bob   |   2
  2 | Bob   |   3
(6 rows)

Common Pitfalls

Common mistakes when using LATERAL with generate_series include:

• Forgetting LATERAL keyword, which causes errors if generate_series references columns from the main table.
• Using JOIN instead of CROSS JOIN LATERAL when no join condition exists.
• Not aliasing the generate_series output, which can cause ambiguous column errors.

sql

/* Wrong: Missing LATERAL keyword */
SELECT t.id, gs.num
FROM my_table t
CROSS JOIN generate_series(1, 3) AS gs(num);

/* Right: Using LATERAL */
SELECT t.id, gs.num
FROM my_table t
CROSS JOIN LATERAL generate_series(1, 3) AS gs(num);

Quick Reference

Keyword/Function	Purpose	Example
LATERAL	Allows function to access columns from preceding FROM items	FROM table CROSS JOIN LATERAL generate_series(...)
generate_series(start, stop, step)	Generates a series of numbers or timestamps	generate_series(1, 5, 1)
CROSS JOIN LATERAL	Joins each row with the function output without join condition	CROSS JOIN LATERAL generate_series(1,3) AS gs(num)
Alias	Names the output column from generate_series	generate_series(...) AS gs(num)

Key Takeaways

Use LATERAL to let generate_series access columns from the main table.

Always alias generate_series output to avoid ambiguous column errors.

Use CROSS JOIN LATERAL when no join condition is needed.

LATERAL with generate_series helps expand rows dynamically per original row.