PostgreSQL | New in 8.0: Instant sequence generator
If you've ever needed a particular sequence of numbers in PostgreSQL, for example for a pivot table, the only way to generate these has been either to create a temporary sequence or to work with a temporary table generated by your application. Beginning with 8.0, PostgreSQL provides the built-in function generate_series(), a set-returning function which can generate any sequence of numbers.
Use it like this:
test=> SELECT * FROM generate_series(1,3); generate_series ----------------- 1 2 3 (3 rows) test=> SELECT * FROM generate_series(1,6,2); generate_series ----------------- 1 3 5 (2 rows) test=> SELECT * FROM generate_series(3,1,-1); generate_series ----------------- 3 2 1 (3 rows)
A practical use could be a statement such as the following, which generates a report of orders for the first seven days of the current month:
SELECT DATE_TRUNC('month', NOW())::DATE + s.d AS date, COUNT(o.*) AS total FROM generate_series(0,6) AS s(d) LEFT JOIN orders o ON (o.order_date = (DATE_TRUNC('month', NOW())::DATE + s.d)::TIMESTAMP) GROUP BY s.d ORDER BY 1
generate_series() is the first built-in function to take advantage of the set-returning functions introduced in version 7.3. Originally created for use in the PostgreSQL backend, it has been made available for general use.