PostgreSQL的generate_series函數應用例子

PostgreSQL中有一個很有用處的內建函數generate_series,可以按不同的規則用來產生一系列的填充資料。

一、文法

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

二、應用例子

1.int類型,不寫步長時預設是1postgres=# 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 TABLEpostgres=# insert into t_kenyon values(1,'192.168.1.254','192.168.2.5');INSERT 0 1postgres=# insert into t_kenyon values(2,'192.168.2.254','192.168.3.5');INSERT 0 1postgres=# insert into t_kenyon values(3,'192.168.3.254','192.168.4.5');INSERT 0 1postgres=# 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.步長為負,且開始值比結束值小