首页 计算机学科 blog sql

# PostgreSQL 的 generate_serise 的函数应用

# 简介

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

# 语法

函数参数类型返回类型描述
generate_serise(start，stop)int 或 bigintsetof int 或 setof bigint (与参数类型相同)生成一个数值序列，从 start 到 stop，<br /> 步进为 1
generate_serise(start，stop，step)int 或 bigintsetof int 或 setof bigint (与参数类型相同)生成一个数值序列，从 start 到 stop,<br /> 步进为 step
generate_serise(start，stop，step_interval)timestamp or timestamp with time zonetimestamp 或 timestamp with time zone (same as argument type)生成一个数值序列，从 <br />start 到 stop，步进为 step

# 实例

# int 类型

  1. 不写步进时默认为 1
select generate_series(1, 10);
-----------------
 generate_series 
-----------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10
(10 rows)
  1. 设置步进
select generate_series(1, 10, 3);
-----------------
 generate_series 
-----------------
               1
               4
               7
              10
(4 rows)
  1. 如果 step 是正数，而 start 大于 stop，那么返回零行。相反，如果 step 是负数，start 小于 stop，则返回零行。如果是 NULL 输入，也产生零行。step 为零则是一个错误
select generate_series(5,1);
-----------------
 generate_series 
-----------------
(0 rows)

NULL inputs

select generate_series(5,null);
-----------------
 generate_series 
-----------------
(0 rows)

step 为零

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

start 大于 stop，step 是负数

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

时间类型

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)
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)

# IP 类型

  1. 建表
create table tbl_david(id int, ip_start inet, ip_stop inet);
  1. 插入数据
insert into tbl_david values (1, '192.168.1.6', '192.168.1.10');
insert into tbl_david values (2, '192.168.2.16', '192.168.2.20');
insert into tbl_david values (3, '192.168.3.116', '192.168.3.120');
  1. 查询数据
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)
  1. generate_serise 生成序列
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)

搞不懂这是为什么我们可以进行拆解

-- sql 执行将 192.168.1.6-192.168.1.10=4 的值 + 192.168.1.6=192.168.1.10
SELECT (ip_stop - ip_start)+ip_start as ip_new FROM tbl_david
---------------
    ip_new     
---------------
192.168.1.10
192.168.2.20
192.168.3.120
(3 rows)
-- 上面的 sql 也类似于如下：
select id, generate_series(0, 4)+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_serise 函数对生成测试数据，批量更新一定规则的数据有比较多的应用场景，使用得当可提升发开效率，另外 IP 的序列生成也是 PG 的一个亮点