GENERATE_SERIES for sequence creation in PostgreSQL - Time & Space Complexity

Time Complexity: GENERATE_SERIES for sequence creation

O(n)

Understanding Time Complexity

We want to understand how the time to create a sequence using GENERATE_SERIES changes as the sequence length grows.

How does the number of generated values affect the work done?

Scenario Under Consideration

Analyze the time complexity of the following PostgreSQL query.

SELECT * FROM GENERATE_SERIES(1, 1000);
    

This query creates a list of numbers from 1 to 1000, one row per number.

Identify Repeating Operations

Look for repeated actions in the query.

• Primary operation: Generating each number in the sequence.
• How many times: Once for each number from start to end (e.g., 1000 times).

How Execution Grows With Input

The work grows directly with how many numbers we want to generate.

Input Size (n)	Approx. Operations
10	10 operations
100	100 operations
1000	1000 operations

Pattern observation: Doubling the sequence length doubles the work.

Final Time Complexity

Time Complexity: O(n)

This means the time to generate the sequence grows in direct proportion to the number of values requested.

Common Mistake

[X] Wrong: "GENERATE_SERIES runs in constant time no matter how big the sequence is."

[OK] Correct: Each number must be created and returned, so more numbers mean more work.

Interview Connect

Understanding how sequence generation scales helps you reason about query performance and data size handling in real projects.

Self-Check

"What if we generate a sequence with a step size greater than 1? How would that affect the time complexity?"

Practice

1. What does the PostgreSQL function GENERATE_SERIES(1, 5) produce?

easy

A. A sequence of numbers from 5 to 1

B. A sequence of numbers from 1 to 5

C. A single number 1

D. An error because step is missing

Solution

1. Step 1: Understand GENERATE_SERIES parameters

   The function takes a start and stop value, here 1 and 5.

2. Step 2: Generate the sequence

   It creates numbers starting at 1, increasing by 1 (default step), up to 5.

3. Final Answer:

   A sequence of numbers from 1 to 5 -> Option B

4. Quick Check:

   GENERATE_SERIES(1,5) = 1,2,3,4,5 [OK]

Hint: Start and stop define range; step defaults to 1 [OK]

Common Mistakes:

• Thinking it counts down without negative step
• Assuming step is required
• Expecting a single value output
• Confusing start and stop order

2. Which of the following is the correct syntax to generate numbers from 10 to 50 stepping by 10 in PostgreSQL?

easy

A. SELECT GENERATE_SERIES(10, 50, 10);

B. SELECT GENERATE_SERIES(10 TO 50 STEP 10);

C. SELECT GENERATE_SERIES FROM 10 TO 50 BY 10;

D. SELECT GENERATE_SERIES(10, 50, STEP 10);

Solution

1. Step 1: Recall correct GENERATE_SERIES syntax

   The function uses parentheses with start, stop, and optional step as numbers separated by commas.

2. Step 2: Match syntax to options

   SELECT GENERATE_SERIES(10, 50, 10); matches the correct syntax: GENERATE_SERIES(10, 50, 10).

3. Final Answer:

   SELECT GENERATE_SERIES(10, 50, 10); -> Option A

4. Quick Check:

   Correct syntax uses commas inside parentheses [OK]

Hint: Use commas inside parentheses for parameters [OK]

Common Mistakes:

• Using keywords like TO or STEP inside function
• Missing commas between parameters
• Adding extra keywords inside parentheses
• Using FROM or BY keywords incorrectly

3. What is the output of this query?

SELECT * FROM GENERATE_SERIES(3, 9, 3);

medium

A. [3, 9]

B. [3, 4, 5, 6, 7, 8, 9]

C. [3, 6, 9]

D. Empty result

Solution

1. Step 1: Identify start, stop, and step values

   Start=3, stop=9, step=3 means numbers increase by 3 from 3 up to 9.

2. Step 2: List generated numbers

   Numbers are 3, then 6 (3+3), then 9 (6+3). It stops at 9.

3. Final Answer:

   [3, 6, 9] -> Option C

4. Quick Check:

   Step 3 increments from 3 to 9 = [3,6,9] [OK]

Hint: Step controls increments; count from start to stop by step [OK]

Common Mistakes:

• Listing all numbers between start and stop ignoring step
• Only showing start and stop values
• Expecting empty result if step is not 1
• Confusing step as a filter

4. Why does this query produce no results?

SELECT * FROM GENERATE_SERIES(5, 1, 1);

medium

A. No error; it returns numbers 5 to 1

B. Step cannot be 1

C. Syntax error due to missing commas

D. No results because start is greater than stop with positive step

Solution

1. Step 1: Analyze start, stop, and step values

   Start=5, stop=1, step=1 means counting up from 5 to 1 by 1, which produces no rows.

2. Step 2: Understand GENERATE_SERIES behavior

   With positive step, start must be less than or equal to stop; otherwise, no rows are generated.

3. Final Answer:

   No results because start is greater than stop with positive step -> Option D

4. Quick Check:

   Positive step requires start ≤ stop [OK]

Hint: Step sign must match direction from start to stop [OK]

Common Mistakes:

• Assuming it counts down with positive step
• Ignoring step sign rules
• Thinking syntax error occurs here
• Expecting reversed sequence automatically

5. You want to generate a sequence of dates from January 1, 2024 to January 10, 2024, stepping by 2 days. Which query correctly does this?

hard

A. SELECT * FROM GENERATE_SERIES('2024-01-01'::date, '2024-01-10'::date, '2 days'::interval);

B. SELECT * FROM GENERATE_SERIES('2024-01-01', '2024-01-10', 2);

C. SELECT * FROM GENERATE_SERIES('2024-01-01'::date, '2024-01-10'::date, 2);

D. SELECT * FROM GENERATE_SERIES('2024-01-01'::timestamp, '2024-01-10'::timestamp, '2');

Solution

1. Step 1: Understand date sequence generation

   To generate dates, start and stop must be dates, and step must be an interval like '2 days'.

2. Step 2: Check each option's correctness

   SELECT * FROM GENERATE_SERIES('2024-01-01'::date, '2024-01-10'::date, '2 days'::interval); uses dates cast properly and interval '2 days' as step, which is correct.

3. Final Answer:

   SELECT * FROM GENERATE_SERIES('2024-01-01'::date, '2024-01-10'::date, '2 days'::interval); -> Option A

4. Quick Check:

   Date range with interval step requires casting and interval [OK]

Hint: Use date::date and step as interval like '2 days' [OK]

Common Mistakes:

• Using integer step for dates
• Not casting strings to date or interval
• Using plain numbers as step with dates
• Casting step incorrectly