PostgreSQL generate_series: Fill Time Gaps, Build Calendars, and Test Data

generate_series is one of PostgreSQL's most useful built-in functions because it creates rows without a table. It can produce a range of integers, dates, or timestamps. That makes it the missing piece in reports that need to show zero-value days, dashboards that need fixed time buckets, and test environments that need realistic data quickly.

The Function in One Minute

-- Integers: start, stop, step
          SELECT generate_series(1, 10, 2);

          -- Dates: one row per day
          SELECT generate_series(
            DATE '2026-07-01',
            DATE '2026-07-07',
            INTERVAL '1 day'
          )::date AS day;

The result is a set of rows, so use it in FROM just like a table.

Use Case 1: Show Days With Zero Orders

A plain aggregation only returns dates that have rows. A dashboard built from it makes a day with zero orders disappear, which is misleading. Generate the expected calendar first, then left join the facts.

WITH days AS (
  SELECT generate_series(
    DATE '2026-07-01',
    DATE '2026-07-07',
    INTERVAL '1 day'
  )::date AS day
)
SELECT
  days.day,
  COALESCE(COUNT(orders.id), 0) AS order_count,
  COALESCE(SUM(orders.total), 0) AS revenue
FROM days
LEFT JOIN orders
  ON orders.created_at >= days.day
 AND orders.created_at < days.day + INTERVAL '1 day'
GROUP BY days.day
ORDER BY days.day;

The range predicate is deliberate. Do not write DATE(orders.created_at) = days.day when created_at is indexed: wrapping the column in a function can prevent the index from being used.

Use Case 2: Find Missing Measurement Intervals

Suppose a collector should write one row per minute. This query exposes every minute where it did not.

WITH expected_minutes AS (
  SELECT generate_series(
    TIMESTAMPTZ '2026-07-13 09:00:00+00',
    TIMESTAMPTZ '2026-07-13 10:00:00+00',
    INTERVAL '1 minute'
  ) AS minute
)
SELECT expected_minutes.minute
FROM expected_minutes
LEFT JOIN metric_samples
  ON metric_samples.recorded_at >= expected_minutes.minute
 AND metric_samples.recorded_at < expected_minutes.minute + INTERVAL '1 minute'
WHERE metric_samples.id IS NULL
ORDER BY expected_minutes.minute;

Use Case 3: Bucket Events Into Five-Minute Windows

For a report, generate every bucket and join events into it. The result keeps empty buckets, which makes charts and alert windows honest.

WITH buckets AS (
  SELECT generate_series(
    date_trunc('hour', now() - INTERVAL '1 hour'),
    date_trunc('hour', now()),
    INTERVAL '5 minutes'
  ) AS bucket_start
)
SELECT
  bucket_start,
  COUNT(events.id) AS event_count
FROM buckets
LEFT JOIN events
  ON events.created_at >= bucket_start
 AND events.created_at < bucket_start + INTERVAL '5 minutes'
GROUP BY bucket_start
ORDER BY bucket_start;

Use Case 4: Generate Test Data Without a Loop

Set-based SQL is faster and easier to clean up than application-side loops. Use deterministic values where a test needs repeatability.

INSERT INTO demo_orders (customer_id, total, created_at)
SELECT
  ((n - 1) % 100) + 1,
  round((10 + random() * 490)::numeric, 2),
  now() - (n || ' minutes')::interval
FROM generate_series(1, 10000) AS n;

Use this only in a development or isolated test database. A convenient generator is still capable of filling a production table very quickly.

Checklist

• Use LEFT JOIN and COALESCE when empty intervals must be visible.
• Join timestamps with a half-open range: >= start AND < end.
• Keep the generated range bounded and choose a sensible step.
• Index the timestamp used by the join, for example CREATE INDEX ON orders (created_at).

In production, a gap is often more important than an average. PG Monitoring can surface missing metric windows alongside the query, agent, or database condition that caused them.