generate_series() for Sequences and Date Spines in SQL

generate_series() produces a sequence of values as rows — dates, timestamps, or integers — that you can query like a table. Its primary use in analytics is building a complete list of every date in a range, so that gaps in your data show up as zeros instead of disappearing from your results.

Here's the problem it solves. Your orders table only has rows for days when orders happened. Group by day and you get a result with no row for quiet days — they simply don't exist in the data. If you need those days to appear with a count of 0, there's nothing to GROUP BY. You need to create those rows synthetically.

generate_series() creates them. It takes a start, an end, and a step:

This produces 366 rows, one per calendar day in 2024. The function expands into rows in FROM like a table. From here, the standard pattern is to wrap it in a CTE and LEFT JOIN to your fact data:

The LEFT JOIN keeps every generated day regardless of whether orders exist. Days with no orders produce NULL in o.id, COUNT ignores NULLs, and COALESCE converts the resulting NULL count to 0. Every day in the range appears in the output.

Here's a monthly version you can run on the ecommerce data:

The one thing that trips people up

When you pass date inputs with an interval step, PostgreSQL returns timestamps, not dates. The ::date cast inside the CTE converts each value back to a plain date. Without it, the join on o.order_date (a date column) may fail or silently produce no matches because the types don't align.

The step controls the resolution. Change '1 day' to '1 week' or '1 month' for weekly or monthly spines. For monthly sequences, PostgreSQL applies calendar arithmetic: adding one month to January 31 gives February 28 (or 29 in a leap year), not March 2. The arithmetic follows real calendar logic, so month-end dates compress forward to the last day of the next month.

generate_series() works with integers too: generate_series(1, 100, 1) produces 100 rows numbered 1 through 100. The date spine pattern is the most common analytical use, but integer sequences appear in test data generation and row-numbering problems as well.

generate_series() in FROM vs in SELECT

You can call generate_series() directly in the FROM clause without wrapping it in a CTE, but the CTE approach is cleaner and gives the sequence a named column. Calling it in the SELECT list is also valid for simple cases: SELECT generate_series(1, 5) returns 5 rows with values 1 through 5. When used in FROM alongside other tables, always wrap it in a CTE or subquery with an explicit column alias — the raw function call produces a column named generate_series, which is awkward to join on.