In this PostgreSQL tutorial, we will learn about “Postgresql generate_series” which helps in generating series of numbers, dates, months and row numbers, etc.
We are going to cover the following topics:
- Postgresql generate_series months
- Postgresql generate_series of dates
- postgresql generate_series group by
- postgres generate series last day of month
- postgresql generate_series from query
- postgres generate_series float
- postgresql generate_series loop
- postgresql generate sequence number
- Postgresql generate_series of numbers
- Postgresql generate_series row number
- Postgresql generate_series insert
- Postgresql generate_series join
- Postgresql generate_series hour
- Postgresql generate_series string
Postgresql generate_series
In Postgresql, we use the generate_series() function to generate series of numbers.
Use the below command to generate series of numbers:
Syntax:
generate_series([start], [stop], [{optional}step/interval]);
Postgresql generate_series function accepts three parameters:
- start: Starting point for generating series.
- stop: The point where the series will stop.
- step/interval: The step point which determines the increment of each step in series.
Note: The default increment for each step of the series is 1.
Let’s understand with some examples:
SELECT generate_series(1,15);

In the above command, series is generated from 1 to 15, the start and stop point is 1 and 15, it does not contain any step point but we can see the increment of each step by 1 value.
As I told you before, that is the default if we don’t provide any step value.
Read PostgreSQL ADD COLUMN + 17 Examples
Postgresql generate_series of months
In Postgresql, to generate series of months we need to know about the DATE operator which can be applied to it.
( + ): It can be used to increase the date, month, hour, interval like a date ‘2001-09-28’ + interval ‘1 hour’ will result in timestamp ‘2001-09-28 01:00:00’.
( – ): It can be used to decrease the date, month, hour, interval like a date ‘2001-09-28’ – interval ‘1 hour’ will result in timestamp ‘2001-09-27 23:00:00’.
There are other operators like *, /.
Use the below command
select DATE '2008-01-01' + (interval '1' month * generate_series(0,11)) as month;

In the above query or output, Year and date are the same but the month changes from 1 to 12 as we can see.
Read PostgreSQL DATE Format + Examples
Postgresql generate_series of dates
In Postgresql, to generate series of dates between two dates use the below command.
SELECT generate_series(date '2004-03-07', '2004-08-16', '1 day')::date AS day;

As we can see above the command, we have explicitly defined the datatype of the series as a date and at the end of the command cast as date (:: date).
If we don’t define the cast type, then the error will occur.
Read PostgreSQL WHERE IN with examples
Postgresql generate_series group by
In Postgresql, to group by series of numbers or dates generated using generat_series function.
First, let’s create a table user_balance with columns usr_id, amount, as_of_date and insert some data into this table.
use the code given below:
- Create a table.
CREATE TABLE user_balance (
usr_id INT,
amount INT,
as_of_date DATE
);
- Insert some data into this table.
INSERT INTO user_balance (usr_id, amount, as_of_date) VALUES
(1, 100, '2021-01-03')
, (1, 50, '2021-01-02')
, (1, 10, '2021-01-01')
, (2, 200, '2021-01-01')
, (3, 30, '2021-01-03');
Now, generate series of dates from 2021-01-01 to 2021-01-03 using the generate_series function, Left join on dates that exist in user_balance.
And then group by with columns usr_id and as_of_date.
use the below command:

Read PostgreSQL CASE with Examples
Postgresql generate_series last day of month
In Postgresql, to generate the last day of the month use the below command.
SELECT last_day + interval '1 month - 1 day' AS last_day_of_mon
FROM generate_series(timestamp '2021-01-01'
, timestamp '2021-01-30'
, interval '1 month') last_day;

In the above output, it shows the last day of the month of date 2021-01-01 to 2021-01-30 is 2021-01-31 which is January 31 Sunday.
If we want to check the last day of the month then change the start and stop point to your preferred date, month, and year ranges in the above command.
Read PostgreSQL WHERE with examples
Postgresql generate_series query
In PostgreSQL, to generate the series of numbers from the query using the below command.
SELECT generate_series(min(quer.series_id),5)
FROM (SELECT * FROM series) AS quer;

In the above command, series is generated from a subquery or from another query that is yellow-colored (SELECT * FROM series) AS quer in the above command, which means the number is generated from the result to another query.
In the above query, we have provided the start point of the generate_series function as the minimum value ( generate_series(min(quer.series_id),5) ) of the column seried_id of the table series.
Read How to Restart PostgreSQL (Linux, Windows, Mac)
Postgresql generate_series of numbers
In Postgresql, to generate series of numbers with step value or increment of each step to 2, use the below command.
SELECT * FROM generate_series(2,10,2);

In the above command, the start point is 2, the stop point is 10, and the increment of each step is 2.
Postgresql generate_series float
In Postgresql, to generate series of float values using the generate_series use the below command.
SELECT generate_series(1.0,10.0, 3.0);

The above output, it shows the float value generated from the start point (1.0) to the stop point ( 10.0 ) with the step point (3.0).
If we want to generate the series of float values then change the start, stop and step point of the generate_series function according to your need.
Read PostgreSQL Export Table to CSV
Postgresql generate_series loop
In Postgresql, to use the generate_series function within the loop for generating series of numbers use the below command:
DO $$
DECLARE
r record;
BEGIN
FOR r IN SELECT * FROM generate_series(1,5) LOOP
RAISE NOTICE '%', r;
END LOOP;
END; $$

In the above output, we loop over the series of numbers that are generated using the generate_sereis function.
Let’s analyze “what is happening within the loop?.“
- r: A variable used to store the current position we are at in the for a loop.
- FOR r IN SELECT * FROM generate_series(1,5): here we have generated the series of numbers from 1 to 5 as records and looping for each record using r variable.
- LOOP: The beginning of our For loop.
- RAISE NOTICE ‘%’, r: here we are printing the series of number in the form of message.
- END LOOP: The end of our For loop.
Read PostgreSQL drop all tables (With examples)
Postgresql generate_series sequence number
Let us see how to generate the sequence of the number using generate_series in PostgreSQL.
For this use the below command.
SELECT generate_series(1+1,10,2)

In the above command, we have generated a sequence of numbers by add 1 to the start point and giving value 2 to the step of the generate_series function.
Read Postgres RegEx
Postgresql generate_series insert
In Postgresql, to insert data into columns using generate_series use the below command.
CREATE TABLE series(series_id INT,series_name VARCHAR);
INSERT INTO series
SELECT generate_series(1,50), 'someseries';
SELECT * FROM series;

In the above command, let’s create the empty table ( series ) with two columns series_id and series_name, and insert data into the columns using the generate_series function.
Read PostgreSQL DATE_PART() Function with examples
Postgresql generate_series join
In Postgresql, to generate series of numbers or something that we want to use with join use the below command.
SELECT * FROM generate_series(5,9)
cross join generate_series(5,generate_series.generate_series) as g2;

In the above command or output, if we see the generate_series (5,9) that cross join with another generate_series(5, generate_series.generate_series).
The left side of the join is generating a series from 5 to 9 while the right side is taking the number from the left side and using it as the max number to generate in a new series.
Read PostgreSQL DROP TABLE + Examples
Postgresql generate_series hour
In Postgresql, to generate hours using generate_series use the below command.
select generate_series(
date_trunc('hour', now()) - '1 day'::interval,
date_trunc('hour', now()),
'1 hour'::interval
) as hour

In the above command, we have accessed the current date and time using the now() function.
- It generated hour between now and yesterday or day before now.
date_trunc() : This function help in extracting specific portion of data and time.
Read PostgreSQL INSERT Multiple Rows
Postgresql generate_series string
In Postgresql, to generate series of strings use the below command:
SELECT(
SELECT concat_ws(' ',name_first, name_last) as generated
FROM (
SELECT string_agg(x,'')
FROM (
select start_arr[ 1 + ( (random() * 25)::int) % 16 ]
FROM
(
select'{CO,GE,FOR,SO,CO,GIM,SE,CO,GE,CA,FRA,GEC,GE,GA,FRO,GIP}'::text[] as start_arr
) syllarr,
generate_series(1, 3 + (generator*0))
) AS comp3syl(x)
) AS comp_name_1st(name_first),
(
SELECT x[ 1 + ( (random() * 25)::int) % 14 ]
FROM (
select '{Ltd,& Co,SARL,SA,Gmbh,United,Brothers,& Sons,International,Ext,Worldwide,Global,2000,3000}'::text[]
) AS z2(x)
) AS comp_name_last(name_last)
)
FROM generate_series(1,7) as generator

In the above output, the command generated 7 random names using generate_series in Postgresql.
Read PostgreSQL DROP COLUMN + 7 examples
Postgresql generate_series row number
In Postgresql, to generate row numbers using generate_series use the below command.
SELECT generate_series( min(series_id ), max( series_id )) as row_num FROM series
GROUP BY series_name;

In the above command first, we have taken the minimum and maximum value of the table name series ( that we created in the sub-section of this tutorial ” Postgresql generate_series insert” ) using the min( seires_id ) and max( series_id ) function.
And then we provided the min value as “start” and max value as a “stop” point to the generate_series function.
As result, it generated the row numbers for each record in the series table.
You may also like:
- PostgreSQL INSERT INTO table + 9 Examples
- PostgreSQL ALTER TABLE + 19 Examples
- PostgreSQL DATE Functions with Examples
- Postgresql cast int + Examples
- How to find primary column name in Postgresql
So in this Postgresql tutorial, we have learned about “Postgresql generate_series” and how to use it with join, dates, month, string, etc.
We have covered the following topics:
- Postgresql generate_series months
- Postgresql generate_series of dates
- postgresql generate_series group by
- Postgres generate series last day of month
- Postgresql generate_series from query
- Postgres generate_series float
- postgresql generate_series loop
- postgresql generate sequence number
- Postgresql generate_series of numbers
- Postgresql generate_series row number
- Postgresql generate_series insert
- Postgresql generate_series join
- Postgresql generate_series hour
- Postgresql generate_series string
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.