Postgresql generate_series with examples

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

Table of Contents

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;

postgresql generate_series of dates — Postgresql generate_series of dates

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;

Postgresql generate_series last day of month — Postgresql generate_series last day of the month

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)

Postgresql generate_series sequence number

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;

postgresql generate_series insert — Postgresql generate_series insert

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;

Postgresql generate_series join — Postgresql generate_series hour

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.