GENERATE_SERIES for sequence creation in PostgreSQL - Mini Project: Build & Apply

Create a Number Sequence Using GENERATE_SERIES in PostgreSQL

📖 Scenario: You are working on a simple inventory system. You need to create a list of product IDs as a sequence of numbers to assign to new products.

🎯 Goal: Build a PostgreSQL query that uses GENERATE_SERIES to create a sequence of numbers from 1 to 10 representing product IDs.

📋 What You'll Learn

Write a query using GENERATE_SERIES to generate numbers from 1 to 10

Assign the generated numbers an alias product_id

Use standard SQL syntax compatible with PostgreSQL

💡 Why This Matters

🌍 Real World

Generating sequences is useful for creating IDs, filling gaps in data, or simulating data in databases.

💼 Career

Database developers and analysts often use GENERATE_SERIES to create test data or to generate ranges for reports.

1

Create a basic GENERATE_SERIES query

Write a PostgreSQL query that uses SELECT and GENERATE_SERIES to generate numbers from 1 to 10.

PostgreSQL

-- Your code here
SELECT GENERATE_SERIES(1, 10);

Hint

Use GENERATE_SERIES(start, stop) to create a sequence of numbers.

2

Add an alias to the generated series

Modify the query to give the generated numbers an alias called product_id using AS product_id.

PostgreSQL

SELECT GENERATE_SERIES(1, 10) AS product_id;
-- Your code here

Hint

Use AS to rename the output column.

3

Add a WHERE clause to filter numbers greater than 5

Add a WHERE clause to the query to only include numbers greater than 5.

PostgreSQL

SELECT GENERATE_SERIES(1, 10) AS product_id
WHERE product_id > 5;
-- Your code here

Hint

Use WHERE product_id > 5 to filter the results.

4

Order the results in descending order

Add an ORDER BY clause to sort the results by product_id in descending order.

PostgreSQL

SELECT GENERATE_SERIES(1, 10) AS product_id
WHERE product_id > 5
ORDER BY product_id DESC;
-- Your code here

Hint

Use ORDER BY product_id DESC to sort from highest to lowest.

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