generate_series函数使用

Table of Contents

• generate_series函数使用
• **一、简介**
• **二、语法**
• **三、实例
• 3.1) int 类型
• 3.2) 时间类型
• 3.3) IP类型

一、简介

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

二、语法

**三、实例

**

3.1) int 类型

a. 不写步进时默认为1

[](javascript:void(0)😉

postgres@[local]:5432=#select generate_series(1, 10);
 generate_series
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10
(10 rows)

b. 设置步进

postgres@[local]:5432=#select generate_series(1, 10, 3);
 generate_series
-----------------
               1
               4
               7
              10
(4 rows)
 

c. 如果step 是正数，而start 大于stop，那么返回零行。相反，如果step 是负数，start 小于stop，则返回零行。如果是NULL 输入，也产生零行。step 为零则是一个错误。

postgres@[local]:5432=# select generate_series(5,1);
 generate_series
-----------------
(0 rows)

javascript:void(0)😉

postgres@[local]:5432=#select generate_series(5,null);
 generate_series
-----------------
(0 rows)

step 为零

postgres@[local]:5432=#select generate_series(5,1,0);
ERROR:  step size cannot equal zero
postgres@[local]:5432=#

start 大于stop，step 是负数

postgres@[local]:5432=#select generate_series(5,1,-1);
 generate_series
-----------------
               5
               4
               3
               2
               1
(5 rows)

postgres@[local]:5432=#

3.2) 时间类型

postgres@[local]:5432=#select generate_series(now(), now() + '7 days', '1 day');
       generate_series
------------------------------
 2021-08-17 23:24:49.03733+08
 2021-08-18 23:24:49.03733+08
 2021-08-19 23:24:49.03733+08
 2021-08-20 23:24:49.03733+08
 2021-08-21 23:24:49.03733+08
 2021-08-22 23:24:49.03733+08
 2021-08-23 23:24:49.03733+08
 2021-08-24 23:24:49.03733+08
(8 rows)

postgres@[local]:5432=#

postgres@[local]:5432=#select generate_series(to_date('20210803','yyyymmdd'), to_date('20210804','yyyymmdd'), '3 hours');
    generate_series
------------------------
 2021-08-03 00:00:00+08
 2021-08-03 03:00:00+08
 2021-08-03 06:00:00+08
 2021-08-03 09:00:00+08
 2021-08-03 12:00:00+08
 2021-08-03 15:00:00+08
 2021-08-03 18:00:00+08
 2021-08-03 21:00:00+08
 2021-08-04 00:00:00+08
(9 rows)

postgres@[local]:5432=#

3.3) IP类型

a. 建表

postgres@[local]:5432=#create table tbl_david(id int, ip_start inet, ip_stop inet);
CREATE TABLE

b. 插入数据

postgres@[local]:5432=#insert into tbl_david values (1, '192.168.1.6', '192.168.1.10');
INSERT 0 1
postgres@[local]:5432=#insert into tbl_david values (2, '192.168.2.16', '192.168.2.20');
INSERT 0 1
postgres@[local]:5432=#insert into tbl_david values (3, '192.168.3.116', '192.168.3.120');
INSERT 0 1
postgres@[local]:5432=#

c. 查看数据

postgres@[local]:5432=#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)

postgres@[local]:5432=#

d. generate_series 生成序列

postgres@[local]:5432=#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)

四、总结

PostgreSQL的generate_series函数对生成测试数据，批量更新一定规则的数据有比较多的应用场景，使用得当可提升开发效率，另外IP的序列生成也是PG的一个亮点。

五、参考

• PostgreSQL官方文档：http://www.postgresql.org/docs/9.2/static/functions-srf.html
• kenyon的个人页面：http://my.oschina.net/Kenyon/blog/75099
• 本文转载https://www.cnblogs.com/mchina/archive/2013/04/03/2997722.html