PostgreSQL – Function – generate_series

Background

Let us generate some test data.

To do so we will use the generate_series function.

Outline

  1. Number
  2. Date

Tasks

Number

SQL

Sample


select
	     tblGS as "interval"

	   , current_date + (tblGS * interval '1 day')
   			as "date1"

 	  , current_date + (tblGS || 'd')::INTERVAL
			as "date2"

	  /*
	   * Random Number between 1 and 1 million
	  */
	  , (random() * 1E6 ) :: int
  			as "random"

   from  generate_series
		(
			  0 -- start
			, 100 -- end
			, 1 -- interval

	    ) tblGS

Output

generate_series_number_01_20190727_0547PM

Date

SQL

Sample

select
	    rank()
            over
               (
                  order by tblGS
               )

              as "interval"

	   , tblGS as "date"

   from  generate_series
		(
			  current_date::timestamp -- start
			, current_date + interval '90 day' -- end
			, '1 day' -- interval

	    ) tblGS

Output

generate_series_date_01_20190727_0552PM

Reference

  1. PostgreSQL
    • Reference
      • Set Returning Functions
        • generate_series
          Link

Summary

Using the generate_series function one is able to reliably generate test data for integers and dates.

As always the random function allows one to generate random decimal numbers.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s