PostgreSQL的generate_series函式應用

553490191發表於2024-03-07
SQL函式

一、簡介

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

二、語法

函式引數型別返回型別描述
generate_series(start, stop) int 或 bigint setof int 或 setof bigint(與引數型別相同) 生成一個數值序列，從start 到 stop，步進為一
generate_series(start, stop, step) int 或 bigint setof int 或 setof bigint(與引數型別相同) 生成一個數值序列，從start 到 stop，步進為step
generate_series(start, stop, step_interval) timestamp or timestamp with time zone timestamp 或 timestamp with time zone(same as argument type) 生成一個數值序列，從start 到 stop，步進為step

三、例項

3.1) int 型別

a. 不寫步進時預設為1

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

b. 設定步進

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

c. 如果step 是正數，而start 大於stop，那麼返回零行。相反，如果step 是負數，start 小於stop，則返回零行。如果是NULL 輸入，也產生零行。step 為零則是一個錯誤。

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

NULL inputs

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

step 為零

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

start 大於stop，step 是負數

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

3.2) 時間型別

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型別

a. 建表

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

b. 插入資料

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. 檢視資料

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 生成序列

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=#

四、總結

PostgreSQL的generate_series函式對生成測試資料，批次更新一定規則的資料有比較多的應用場景，使用得當可提升開發效率，另外IP的序列生成也是PG的一個亮點。

相關文章