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.