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);

Need a 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

Need a 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

Need a 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

Need a hint?

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