GENERATE_SERIES for sequence creation in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the GENERATE_SERIES function do in PostgreSQL?

It creates a set of rows with a sequence of numbers or timestamps between a start and stop value, optionally with a step size.

beginner

How do you generate numbers from 1 to 5 using GENERATE_SERIES?

Use SELECT * FROM GENERATE_SERIES(1, 5); which returns rows with values 1, 2, 3, 4, 5.

beginner

What is the default step size in GENERATE_SERIES if not specified?

The default step size is 1 for numeric sequences.

intermediate

Can GENERATE_SERIES create sequences of timestamps?

Yes, it can generate sequences of timestamps or dates by specifying start, stop, and interval values like '1 day'.

intermediate

What happens if the step size in GENERATE_SERIES is negative?

The sequence counts down from start to stop, generating values decreasing by the step size until it reaches or passes the stop value.

What will SELECT * FROM GENERATE_SERIES(3, 7, 2); return?

A 2, 4, 6

B 3, 4, 5, 6, 7

C 3, 5, 7

D 7, 5, 3

The series starts at 3, increments by 2, so it returns 3, 5, and 7.

Which of these is a valid use of GENERATE_SERIES for dates?

A GENERATE_SERIES(1, 5, '1 day')

B GENERATE_SERIES('2024-01-01', '2024-01-05', '1 day')

C GENERATE_SERIES('2024-01-01', '2024-01-05', 1)

D GENERATE_SERIES('1 day', '5 day', 1)

You must specify start and stop as dates or timestamps and the step as an interval like '1 day'.

What is the output of GENERATE_SERIES(5, 1, -1)?

A 5, 4, 3, 2, 1

B 1, 2, 3, 4, 5

C 5, 6, 7, 8, 9

D No output, invalid step

Negative step counts down from 5 to 1.

If you omit the step in GENERATE_SERIES(1, 3), what is the step size?

A 0

B Undefined

C 3

D 1

The default step size is 1.

Which statement about GENERATE_SERIES is true?

A It returns a set of rows.

B It only works with integers.

C It returns a single value.

D It cannot generate sequences backwards.

GENERATE_SERIES returns multiple rows forming a sequence.

Explain how to use GENERATE_SERIES to create a sequence of numbers from 10 to 20 with a step of 2.

Think about start, stop, and step parameters.

Describe how GENERATE_SERIES can be used to generate dates for each day in January 2024.

Use date or timestamp types with interval steps.

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