Generate a sequence of numbers

A numbers table on the fly — useful for gap-filling, calendars, and sample data. Each engine has a generator; the recursive CTE works everywhere.

-- PostgreSQL, DuckDB
SELECT n FROM generate_series(1, 10) AS n;

-- BigQuery
SELECT n FROM UNNEST(GENERATE_ARRAY(1, 10)) AS n;

-- Snowflake
SELECT seq4() + 1 AS n FROM TABLE(GENERATOR(ROWCOUNT => 10));

-- MySQL 8+, SQLite (and portable everywhere): a recursive CTE
WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 10
)
SELECT n FROM seq;

Use it to fill gaps — e.g. every day in a range, even days with no rows:

-- PostgreSQL: a row per day, then LEFT JOIN your data onto it
SELECT d::date AS day, COALESCE(s.total, 0) AS total
FROM generate_series('2026-01-01'::date, '2026-01-31'::date, interval '1 day') AS d
LEFT JOIN daily_sales s ON s.day = d::date
ORDER BY day;

Step and direction. generate_series(1, 10, 2) steps by 2 (Postgres/DuckDB); BigQuery GENERATE_ARRAY(1, 10, 2). For a recursive CTE, change the + 1 and the WHERE bound. Watch the recursion limit on huge ranges — MySQL’s cte_max_recursion_depth defaults to 1000; raise it or use a real generator. For date ranges across dialects, see the companion site datesql.