Pg中有一个很有用处的内置函数generate_series,可以按不同的规则用来产生一系列的填充数据。
一、语法
generate_series(start,stop) --int or bigint generate_series(start,stop,step) --int or bigint generate_series(start,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','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 2 | 192.168.2.254 | 192.168.3.5 3 | 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.步长为负,且开始值比结束值小
四、参考:
http://www.postgresql.org/docs/9.0/static/functions-srf.html http://blog.163.com/digoal@126/blog/static/163877040201111805555263/