Sequence generators

These functions generate rows and sequences for creating test data. Use them together with random value generators to populate tables with test data.

Choosing the right function

Key difference for timestamps:

• generate_series is standalone and range-based: "give me timestamps from A to B"
• timestamp_sequence requires long_sequence and is count-based: "give me N timestamps starting at A"

Function reference

• long_sequence - generate N rows for use with random functions
• generate_series - generate arithmetic series (long, double, or timestamp)
• timestamp_sequence - generate monotonically increasing timestamps

long_sequence

Generates a pseudo-table with a specified number of rows. This is the primary function for creating test data in QuestDB.

• long_sequence(num_rows) - generates rows with a random seed.
• long_sequence(num_rows, seed1, seed2) - generates rows deterministically using the provided seed values.

The function serves two purposes:

1. Generates a pseudo-table with an ascending series of LONG numbers starting at 1 (accessible via column x)
2. Provides the seed for pseudo-randomness to all random value functions

:::tip

Deterministic procedural generation makes it easy to test on vast amounts of data without moving large files across machines. Using the same seed on any machine at any time will consistently produce the same results for all random functions.

:::

Arguments:

• num_rows is a long representing the number of rows to generate.
• seed1 and seed2 are long numbers that combine into a long128 seed for deterministic generation.

Return value:

Returns a pseudo-table with a single column x of type long, containing values from 1 to num_rows.

Examples:

SELECT x, rnd_double()
FROM long_sequence(5);

SELECT x, x * x AS square
FROM long_sequence(5);

SELECT rnd_double()
FROM long_sequence(2, 128349234, 4327897);

:::note

The results below will be the same on any machine at any time as long as they use the same seed in long_sequence.

:::

generate_series

Generates a pseudo-table containing an arithmetic series in a single column. Use it when you need a specific range of values rather than a specific number of rows.

You can call it in isolation (generate_series(...)) or as part of a SELECT statement (SELECT * FROM generate_series(...)).

The start and end values are interchangeable. Use a negative step value to obtain a descending series. The series is inclusive on both ends.

Variants:

• generate_series(start_long, end_long) - generates a series of longs with step 1.
• generate_series(start_long, end_long, step_long) - generates a series of longs with custom step.
• generate_series(start_double, end_double) - generates a series of doubles with step 1.
• generate_series(start_double, end_double, step_double) - generates a series of doubles with custom step.
• generate_series(start_timestamp, end_timestamp, step_period) - generates a series of timestamps with a period step.
• generate_series(start_timestamp, end_timestamp, step_micros) - generates a series of timestamps with microsecond step.

QuestDB determines which variant to use based on the argument types.

Arguments:

• start is the first value in the series (inclusive).
• end is the last value in the series (inclusive).
• step is the increment between values (default: 1 for numeric types).

For timestamp variants, step_period is a string using the format:

Use negative values for descending series (e.g., '-1d').

Return value:

The column type of the pseudo-table matches the argument types: LONG, DOUBLE, TIMESTAMP, or TIMESTAMP_NS (if nanosecond timestamps are provided).

Examples:

Long series:

generate_series(-3, 3);

generate_series(3, -3, -1);

Double series:

generate_series(-3d, 3d);

generate_series(3d, -3d, -1.5d);

Timestamp series:

generate_series('2025-01-01', '2025-02-01', '5d');

generate_series('2025-01-01', '2025-02-01', '-5d');

generate_series(
    '2025-01-01T00:00:00Z'::timestamp,
    '2025-01-01T00:05:00Z'::timestamp,
    60_000_000 -- 1 minute in microseconds
);

generate_series(
    to_timestamp_ns('2025-01-01T00:00:00', 'yyyy-MM-ddTHH:mm:ss'),
    '2025-01-01T00:00:00.000001',
    '500n'
);

timestamp_sequence

Generates monotonically increasing timestamps when used with long_sequence(). Unlike generate_series, this function generates a single value per row and requires long_sequence() to produce multiple rows.

• timestamp_sequence(startTimestamp, step) - generates timestamps starting at startTimestamp, incrementing by step microseconds per row.

The step can be:

• A fixed value, resulting in evenly-spaced timestamps
• A random function invocation (e.g., rnd_short(1, 5) * 100000L), resulting in timestamps that grow by random intervals

Arguments:

• startTimestamp is the starting (lowest) timestamp in the sequence.
• step is the interval in microseconds between consecutive timestamps.

Return value:

Return type is TIMESTAMP. If a TIMESTAMP_NS or a date literal with nanosecond resolution is provided, the return type is TIMESTAMP_NS.

Examples:

SELECT x, timestamp_sequence(
    to_timestamp('2019-10-17T00:00:00', 'yyyy-MM-ddTHH:mm:ss'),
    100000L -- 100ms in microseconds
) AS ts
FROM long_sequence(5);

SELECT x, timestamp_sequence(
    to_timestamp('2019-10-17T00:00:00', 'yyyy-MM-ddTHH:mm:ss'),
    rnd_short(1, 5) * 100000L -- 100-500ms random intervals
) AS ts
FROM long_sequence(5);