PostgreSQL中的generate_series函数

PostgreSQL中的generate_series函数

一、简介

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

三、例子

1、int类型

• 不写步进时默认为1

  select generate_series(1,10) as num;
  

  

• 设置步进为2

  select generate_series(1,10,2) as num;
  

  

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

  start 大于stop

  yang=# select generate_series(10,5) as num;
  num
  -----
  (0 rows)
  

  start 小于stop，step 是负数

  yang=# select generate_series(1,5,-1) as num;
  num
  -----
  (0 rows)
  

  NULL 输入，也产生零行

  yang=# select generate_series(1,null) as num;
  num
  -----
  (0 rows)
  

  step 为零

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

2、时间类型

• 生成连续日期

  yang=# select generate_series(now(),now()+'8 day','1 day') as num;
             num
  ------------------------------
  2024-05-11 21:42:47.81037+08
  2024-05-12 21:42:47.81037+08
  2024-05-13 21:42:47.81037+08
  2024-05-14 21:42:47.81037+08
  2024-05-15 21:42:47.81037+08
  2024-05-16 21:42:47.81037+08
  2024-05-17 21:42:47.81037+08
  2024-05-18 21:42:47.81037+08
  2024-05-19 21:42:47.81037+08
  (9 rows)
  

• 生成时间间隔

  yang=# select generate_series(to_date('20240511','yyyymmdd'),to_date('20240512','yyyymmdd'),'5 hour') as num;
             num
  ------------------------
  2024-05-11 00:00:00+08
  2024-05-11 05:00:00+08
  2024-05-11 10:00:00+08
  2024-05-11 15:00:00+08
  2024-05-11 20:00:00+08
  (5 rows)
  

四、总结

PostgreSQL的generate_series函数对生成测试数据，批量更新数据有比较多的应用场景，使用得当可以提高工作效率。

另外IP的序列生成也是PG的一个亮点。

• 例如：生成一批随机数

  yang=# select floor(random()*10+1) num1,round(random()::numeric,2) num2 from generate_series(1,10);
  num1 | num2
  ------+------
    6 | 0.22
    6 | 0.51
    8 | 0.84
    7 | 0.57
   10 | 0.46
    9 | 0.56
    7 | 0.84
    7 | 0.27
    7 | 0.62
    2 | 0.10
  (10 rows)