PostgreSQL: Generate Number series and Date time series or sequence

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing the use generate_series() of PostgreSQL. I already used this function many times in different PG articles.

But here, sharing few examples of this function because people asking how to generate series in PostgreSQL.

PostgreSQL 9.5: Introduced BRIN – Block Range Index with Performance Report

I am sharing an example of date generation and number generation.
Check the below demonstration:

Create a sample table for Date Series:

CREATE TABLE tbl_ItemTransactions

(

TranID SERIAL

,TransactionDate TIMESTAMPTZ

,TransactionName TEXT

);

Generate the date series and insert into table:

INSERT INTO tbl_ItemTransactions

(TransactionDate, TransactionName)

SELECT x, 'dbrnd'

FROM generate_series('2008-01-01 00:00:00'::timestamptz, '2018-02-01 00:00:00'::timestamptz,'1 days'::interval) a(x);

Generate 1000 numbers:

1 2	SELECT a.x FROM generate_series(1, 1000) as a(x)

Generate 1000 duplicate numbers:

1 2	SELECT a.x from generate_series(1, 1000) as a(x), generate_series(1,2)

Feb 20, 2018Anvesh Patel