PostgreSQL的generate_series函数应用例子

PostgreSQL中有一个很有用处的内置函数generate_series,可以按不同的规则用来产生一系列的填充数据。

一、语法

generate_series(start,stop)                --int or bigint
generate_series(start,stop,step)           --int or bigint
generate_series(start,stop, step interval) --timestamp or timestamp with time zone

二、应用例子

1.int类型,不写步长时默认是1
postgres=# select generate_series(1,10);
 generate_series 
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10
(10 rows)

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

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

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

2.时间类型
postgres=# select generate_series(now(),now() + '7 day','1 day');
        generate_series        
-------------------------------
 2012-08-27 22:12:40.915368+08
 2012-08-28 22:12:40.915368+08
 2012-08-29 22:12:40.915368+08
 2012-08-30 22:12:40.915368+08
 2012-08-31 22:12:40.915368+08
 2012-09-01 22:12:40.915368+08
 2012-09-02 22:12:40.915368+08
 2012-09-03 22:12:40.915368+08
(8 rows)

postgres=# select generate_series(to_date('20120827','yyyymmdd'),to_date('20120828','yyyymmdd'),'3 h');
    generate_series     
------------------------
 2012-08-27 00:00:00+08
 2012-08-27 03:00:00+08
 2012-08-27 06:00:00+08
 2012-08-27 09:00:00+08
 2012-08-27 12:00:00+08
 2012-08-27 15:00:00+08
 2012-08-27 18:00:00+08
 2012-08-27 21:00:00+08
 2012-08-28 00:00:00+08
(9 rows)
 
3.IP类型
postgres=# create table t_kenyon(id int,ip_start inet,ip_end inet);
CREATE TABLE
postgres=# insert into t_kenyon values(1,'192.168.1.254','192.168.2.5');
INSERT 0 1
postgres=# insert into t_kenyon values(2,'192.168.2.254','192.168.3.5');
INSERT 0 1
postgres=# insert into t_kenyon values(3,'192.168.3.254','192.168.4.5');
INSERT 0 1

postgres=# select * from t_kenyon;
 id |   ip_start    |   ip_end    
----+---------------+-------------
  1 | 192.168.1.254 | 192.168.2.5
  1 | 192.168.2.254 | 192.168.3.5
  1 | 192.168.3.254 | 192.168.4.5
(3 rows)

postgres=# select id,generate_series(0,ip_end-ip_start)+ip_start as ip_new from t_kenyon;
 id |    ip_new     
----+---------------
  1 | 192.168.1.254
  1 | 192.168.1.255
  1 | 192.168.2.0
  1 | 192.168.2.1
  1 | 192.168.2.2
  1 | 192.168.2.3
  1 | 192.168.2.4
  1 | 192.168.2.5
  2 | 192.168.2.254
  2 | 192.168.2.255
  2 | 192.168.3.0
  2 | 192.168.3.1
  2 | 192.168.3.2
  2 | 192.168.3.3
  2 | 192.168.3.4
  2 | 192.168.3.5
  3 | 192.168.3.254
  3 | 192.168.3.255
  3 | 192.168.4.0
  3 | 192.168.4.1
  3 | 192.168.4.2
  3 | 192.168.4.3
  3 | 192.168.4.4
  3 | 192.168.4.5
(24 rows)

三、总结
Pg的generate_series函数对生成测试数据,批量更新一定规则的数据有比较多的应用场景，使用得当可提升开发效率。另外IP的序列生成也是PG的一个亮点。
有两种情况不能生成数据：
1.步长为正,且开始值比结束值大
2.步长为负,且开始值比结束值小