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)

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() + &＃39;7 day&＃39;,&＃39;1 day&＃39;);
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(&＃39;20120827&＃39;,&＃39;yyyymmdd&＃39;),to_date(&＃39;20120828&＃39;,&＃39;yyyymmdd&＃39;),&＃39;3 h&＃39;);
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,&＃39;192.168.1.254&＃39;,&＃39;192.168.2.5&＃39;);
INSERT 0 1
postgres=# insert into t_kenyon values(2,&＃39;192.168.2.254&＃39;,&＃39;192.168.3.5&＃39;);
INSERT 0 1
postgres=# insert into t_kenyon values(3,&＃39;192.168.3.254&＃39;,&＃39;192.168.4.5&＃39;);
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.步长为负,且开始值比结束值小