How to use a sequence in a SELECT statement

You can use the generate_series() builtin function like this:

SELECT generate_series(1, 10) AS sequence_number;

sequence_number
1
2
3
4
5
6
7
8
9
10

#Customizing a generated series

The function takes the form generate_series(start, stop, [step])

SELECT generate_series(4, 200, 2) AS even_numbers;

#Series with timestamps

SELECT generate_series(
         '2023-01-01 00:00:00'::timestamp,
         '2023-01-07 23:59:59'::timestamp,
         '1 day'::interval
       ) AS timestamp_sequence;

timestamp_sequence
2023-01-01 00:00:00
2023-01-02 00:00:00
2023-01-03 00:00:00
2023-01-04 00:00:00
2023-01-05 00:00:00
2023-01-06 00:00:00
2023-01-07 00:00:00

SELECT timestamp_sequence, EXTRACT(HOUR FROM timestamp_sequence) AS hour_of_day
FROM generate_series(
       '2023-01-01 00:00:00'::timestamp,
       '2023-01-07 23:59:59'::timestamp,
       '1 hour'::interval
     ) AS timestamp_sequence;

timestamp_sequence	hour_of_day
2023-01-01 00:00:00	0
2023-01-01 01:00:00	1
2023-01-01 02:00:00	2
2023-01-01 03:00:00	3
2023-01-01 04:00:00	4
2023-01-01 05:00:00	5
2023-01-01 06:00:00	6
2023-01-01 07:00:00	7
2023-01-01 08:00:00	8
2023-01-01 09:00:00	9
2023-01-01 10:00:00	10
...	...