GENERATE_SERIES for sequence creation in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple generate_series query

What is the output of the following SQL query?

SELECT * FROM generate_series(1, 5);

PostgreSQL

SELECT * FROM generate_series(1, 5);

A [5, 4, 3, 2, 1]

B [1, 2, 3, 4]

C [0, 1, 2, 3, 4, 5]

D [1, 2, 3, 4, 5]

💡 Hint

generate_series(start, stop) produces a sequence including both start and stop values.

The generate_series function in PostgreSQL generates a set of integers from the start value to the stop value inclusive. So generate_series(1,5) returns 1,2,3,4,5.

❓ query_result

intermediate

Output of generate_series with step value

What is the output of this query?

SELECT * FROM generate_series(2, 10, 3);

PostgreSQL

SELECT * FROM generate_series(2, 10, 3);

A [2, 5, 8]

B [2, 3, 5, 8, 10]

C [2, 5, 8, 11]

D [2, 4, 6, 8, 10]

💡 Hint

The third argument is the step size between numbers.

generate_series(2,10,3) starts at 2 and adds 3 each time until it reaches or passes 10. The values are 2, 5, 8. 11 is beyond 10 so it is excluded.

📝 Syntax

advanced

Identify the syntax error in generate_series usage

Which option contains a syntax error when using generate_series in PostgreSQL?

A SELECT * FROM generate_series(1, 10, 2);

B SELECT * FROM generate_series(1 TO 10);

C SELECT * FROM generate_series(1, 10);

D SELECT * FROM generate_series(5, 1, -1);

💡 Hint

Check the function call syntax carefully.

The generate_series function uses parentheses and commas to separate arguments. '1 TO 10' is not valid syntax in PostgreSQL function calls.

🧠 Conceptual

advanced

Understanding generate_series with negative step

What will be the output of this query?

SELECT * FROM generate_series(5, 1, -1);

PostgreSQL

SELECT * FROM generate_series(5, 1, -1);

A [5, 4, 3, 2, 1]

B [1, 2, 3, 4, 5]

C [] (empty set)

D [5, 4, 3, 2]

💡 Hint

Negative step counts down from start to stop inclusive.

generate_series(5,1,-1) counts down from 5 to 1 by subtracting 1 each step, so it returns 5,4,3,2,1.

❓ optimization

expert

Efficiently generate a large sequence with generate_series

You want to generate a sequence of integers from 1 to 1,000,000 in PostgreSQL. Which query is the most efficient?

A SELECT * FROM generate_series(1, 1000000) WHERE generate_series % 2 = 0;

B SELECT * FROM generate_series(1, 1000000) ORDER BY 1 DESC;

C SELECT * FROM generate_series(1, 1000000);

D SELECT * FROM generate_series(1, 1000000) LIMIT 1000000;

💡 Hint

Consider the cost of filtering and ordering large sequences.

Option C directly generates the sequence without extra filtering or sorting, making it the most efficient. Options A and C add extra operations that slow down the query. Option C is redundant since generate_series already produces exactly 1,000,000 rows.