generate_series function Application of PostgreSQL

First, Introduction

PostgreSQL has a very useful built-in function generate_series, which can produce a series of padding data according to different rules.

Second, the grammar

function	parameter Type	return type	Description
Generate_series (Start, stop)	int or bigint	setof int or setof bigint (same as parameter type)	Generates a sequence of values from start to stop, stepping to a
Generate_series (Start, stop, step)	int or bigint	setof int or setof bigint (same as parameter type)	Generates a sequence of values, from start to stop, stepping to step
Generate_series (Start, stop, Step_interval)	Timestamp or timestamp with time zone	Timestamp or timestamp with time zone (same as argument type)	Generates a sequence of values, from start to stop, stepping to step

Third, examples

3.1) int type

A. Default to 1 if stepping is not written

david=# select generate_series(1, 10);
 generate_series 
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10
(10 rows)

david=# 
david=# 

B. Setting the stepping

david=# select generate_series(1, 10, 3);
 generate_series 
-----------------
               1
               4
               7
              10
(4 rows)

david=# 

C. If step is a positive number and start is greater than stop, then 0 rows are returned. Conversely, if step is a negative number and start is less than stop, 0 rows are returned. If it is a null input, it also produces 0 rows. Step zero is a mistake.

david=# select generate_series(5,1);
 generate_series 
-----------------
(0 rows)

david=# 

NULLInputs

david=# select generate_series(5,null);
 generate_series 
-----------------
(0 rows)

david=#


  

	
   


  

Step is zero

david=# select generate_series(5,1,0);
ERROR:  step size cannot equal zero
david=#

Start greater than Stop,step is negative

david=# select generate_series(5,1,-1);
 generate_series 
-----------------
               5
               4
               3
               2
               1
(5 rows)

david=#

3.2) Time type

david=# select generate_series(now(), now() + ‘7 days‘, ‘1 day‘);
        generate_series        
-------------------------------
 2013-04-03 14:22:26.391852+08
 2013-04-04 14:22:26.391852+08
 2013-04-05 14:22:26.391852+08
 2013-04-06 14:22:26.391852+08
 2013-04-07 14:22:26.391852+08
 2013-04-08 14:22:26.391852+08
 2013-04-09 14:22:26.391852+08
 2013-04-10 14:22:26.391852+08
(8 rows)

david=#
 
david=# select generate_series(to_date(‘20130403‘,‘yyyymmdd‘), to_date(‘20130404‘,‘yyyymmdd‘), ‘3 hours‘);  
    generate_series     
------------------------
 2013-04-03 00:00:00+08
 2013-04-03 03:00:00+08
 2013-04-03 06:00:00+08
 2013-04-03 09:00:00+08
 2013-04-03 12:00:00+08
 2013-04-03 15:00:00+08
 2013-04-03 18:00:00+08
 2013-04-03 21:00:00+08
 2013-04-04 00:00:00+08
(9 rows)

david=#

3.3) IP Type

A. Build a table

david=# create table tbl_david(id int, ip_start inet, ip_stop inet);
CREATE TABLE
david=#

B. Inserting data

david=# insert into tbl_david values (1, ‘192.168.1.6‘, ‘192.168.1.10‘);   
INSERT 0 1
david=# insert into tbl_david values (2, ‘192.168.2.16‘, ‘192.168.2.20‘);  
INSERT 0 1
david=# insert into tbl_david values (3, ‘192.168.3.116‘, ‘192.168.3.120‘); 
INSERT 0 1
david=#

C. View data

david=# select * from tbl_david ;
 id |   ip_start    |    ip_stop    
----+---------------+---------------
  1 | 192.168.1.6   | 192.168.1.10
  2 | 192.168.2.16  | 192.168.2.20
  3 | 192.168.3.116 | 192.168.3.120
(3 rows)

david=#

D. Generate_series generation sequence

david=# select id, generate_series(0, ip_stop-ip_start)+ip_start as ip_new from tbl_david ;
 id |    ip_new     
----+---------------
  1 | 192.168.1.6
  1 | 192.168.1.7
  1 | 192.168.1.8
  1 | 192.168.1.9
  1 | 192.168.1.10
  2 | 192.168.2.16
  2 | 192.168.2.17
  2 | 192.168.2.18
  2 | 192.168.2.19
  2 | 192.168.2.20
  3 | 192.168.3.116
  3 | 192.168.3.117
  3 | 192.168.3.118
  3 | 192.168.3.119
  3 | 192.168.3.120
(15 rows)

david=#

Iv. Summary

PostgreSQL's generate_series function to generate test data, batch update a certain number of rules of the data there are more application scenarios, the use of appropriate to improve the development efficiency, the other IP sequence generation is also a highlight of pg.

generate_series function Application of PostgreSQL