PostgreSQL函数｜内置函数之GENERATE_SERIES详解（一）

背景

近期在做一些数据处理的工作，工作中使用其他项目组平台来做数据开发，在数据开发过程中，使用了PostgreSQL的一个内置函数 GENERATE_SERIES。在本地测试执行时，并没有什么问题出现，而在项目组开发的平台执行SQL脚本时报错，具体错误提示信息如下：

[ERRORI>> 2024-06-13 09:41:56-执行语句异常【EXECUTE SOL GREENPLUM ERROR{message=ERROR: function generate_series(double precision, double precision, integer) does not exist}

在拿到错误信息时，第一想法就是直接拿着SQL找到数据库管理员，让其协助测试下这个内置函数（GENERATE_SERIES）是否可用。当数据库管理员的发回截图时，我瞬间被懵掉了，这个函数的确存在，而且也可以执行，并有正确的结果输出出来，那么错误的具体原因是什么？为何会出现这个问题呢？下面就开始随我一探究竟吧。

1关于内置函数

对于SQL中的内置函数，应该不会太陌生。所谓内置，就是在安装服务软件后就已存在的函数，它对应的应该是UDF（用户自定义函数）。在SQL中，有许多内置函数（或称为系统函数、内建函数）可用于处理数据。这些函数允许你执行复杂的计算、转换数据类型、处理字符串和日期等。通俗一点讲就是预定义好的功能，用于执行特定任务（也可以说是一个工具类），如处理数据、进行数学运算、处理字符串、日期和时间等。不同的数据库系统（如MySQL、PostgreSQL、SQL Server、Oracle等）可能支持不同的函数集，但许多基本函数在各种系统中都是通用的。

2GENERATE_SERIES

2.1 释义

【函数释义】：数据集函数，按照一定参数规则返回数据集。主要用于生成示例数据或一些有规律的记录，generate_series允许您生成一组从某个点开始，到另一个点结束的数据，并可选择设置递增值。

当前测试（实验）环境为PostgreSQL 16.2，所有操作均以这个版本进行测试（这个后续会有说明）。

2.2 用法

在PostgreSQL的官网有如下三种的用法和描述。

【语法结构】

generate_series ( start integer, stop integer [, step integer ] ) → setof integer
generate_series ( start bigint, stop bigint [, step bigint ] ) → setof bigint
generate_series ( start numeric, stop numeric [, step numeric ] ) → setof numeric
generate_series ( start timestamp, stop timestamp, step interval ) → setof timestamp
generate_series ( start timestamp with time zone, stop timestamp with time zone, step interval [, timezone text ] ) → setof timestamp with time zone

【参数】

从上面的函数结构中，可以看到generate_series这个函数支持三种类型的输入参数，分别为integer、bigint、numeric 。如果对这三个函数还没有理解，可以去官网查阅具体的使用方法，或者期待下一篇吧，到时候会详细讲讲。其中，第一个输入参数为开始参数，第二个参数为结束参数，第三个参数（可选，默认不填写时，步长为 1 ）为步长。而在返回结果中也是对应到输入参数。

【integer示例】

举例说明下吧，例如我想返回数字 1 到 8 所在范围中步长为1的所有数据（这里可以理解为从1开始计算，在8范围内的所有数据，如果使用N表示数据集，从数学公式上面可以表示为 1 <= N <= 8 或者集合 [1, 8]）。当步长为负数时（例如 -2 ），则相反。具体的计算步骤为：

用法一：start <= stop，step = 1时，如果步长为1时，那就是

n1为，start = 1

n2为，n1+1 = 2

n3为，n2+1 = 3

n4为，n3+1 = 4

……

n8为，n7+1 = 8

n9为，n8+1 = 9，此时，结果9>8，不满足在[1，8]集合的范围内，所以舍弃，最终的返回结果如下：

用法二：start <= stop，step = 2时，如果步长为2时，那就是

n1为，start = 1

n2为，n1+2 = 3

n3为，n2+2 = 5

n4为，n3+2 = 7

n5为，n4+2 = 9，此时，结果9>8，不满足在[1，8]集合的范围内，所以舍弃，最终的返回结果如下：

用法三：start >= stop，step = -2时，如果步长为 -2 时（反向获取数据），那就是

n1为，start = 8

n2为，n1+(-2) = 6

n3为，n2+(-2) = 4

n4为，n3+(-2) = 2

n5为，n4+(-2) = 0，此时，结果0<1，不满足在[8，1]集合的范围内，所以舍弃，最终的返回结果如下：

示例中涉及到的代码如下：

select generate_series(1, 8, 1) as N ;

select generate_series(1, 8, 2) as N ;

select generate_series(8, 1, -2) as N ;

2.3 注意

这里需要特别注意下，返回 0 行的情况有如下几个：当步长为正时，如果start大于stop，则返回零行。相反，当步长为负时，如果start小于stop，则返回零行。NULL输入也返回零行。步长不能为 0 。

当 start 大于 stop时，此时开始值为8，加上步长 2 后，为 10，不符合数据集规则，则返回0行。

当 start 小于 stop时，此时开始值为8，加上步长-2 后，为 6，不符合数据集规则，则返回0行。

select generate_series(8, 1, 2) as N ;
select generate_series(1, 8, -2) as N ;

错误根因分析

下面是我写SQL的步骤：

步骤1 套用语法后的原始SQL

select generate_series(2019, 2023, 1) as N;
2019
2020
2021
2022
2023

步骤2 根据业务，获取参数年份前5年的年份数据集，此时想获取的数据集为 2019，2020，2021，2022，2023。

select generate_series('2023' - 4, '2023', 1) as N;
2019
2020
2021
2022
2023

步骤3 我在这里又做了一次画蛇添足，将'2023'转为了 timestamp 类型，最终导致在 PostgreSQL 12版本中无法执行。

在排查根因分析时，错误信息提示我有一个double的参数，可能是哪里写错了，后来才发现是版本的问题。这个错误提示也是让我摸不到头脑。

• 在PostgreSQL最新开发版本17.x 中
• 在PostgreSQL 13.x～16.x以及
• 在PostgreSQL 12.x 以及以前版本中

总结

遇事不要慌，多看看官网，多找找原因，实在不行多翻一翻其他的博文，总有能帮助你的那一刻。下一篇总结下其他参数的使用方法。

[引用]

1. PostgreSQL 16(generate_series)：https://www.postgresql.org/docs/16/functions-srf.html
2. PostgreSQL 12(generate_series)：https://www.postgresql.org/docs/12/functions-srf.html

我正在参与2024腾讯技术创作特训营最新征文，快来和我瓜分大奖！