迁移 Oracle 到 PostgreSQL: 单行函数和聚集函数

七月 18, 2023

摘要：在本教程中，您将学习如何将 Oracle 的内置 SQL 函数，包括单行函数和聚集函数，迁移到 PostgreSQL。

Oracle 根据用作输入和生成作为输出的行数，提供了两种主要类别的内置 SQL 函数。

单行函数（也称为标量函数）为查询的表或视图的每一行返回一个结果。您可以将它们与SELECT语句中的WHERE子句、START WITH子句、CONNECT BY子句和HAVING子句一起使用。单行函数根据数据类型分为几组，比如NUMERIC函数、CHAR函数、DATETIME函数等。
聚合函数（也称为组函数）用于将一组值汇总为单个结果。示例包括AVG、MIN、MAX、SUM、COUNT、LISTAGG、FIRST和LAST。

Oracle 用法

有关 Oracle 和 PostgreSQL 单行函数的比较，请参阅以下部分。

Oracle 19 增加了使用新关键字DISTINCT消除LISTAGG函数结果中重复项的功能。

Oracle 19 引入了几个新的位图 SQL 聚合函数（BITMAP_BUCKET_NUMBER、BITMAP_BIT_POSITION和BITMAP_CONSTRUCT_AGG），有助于加快COUNT DISTINCT操作速度。

有关详细信息，请参阅 Oracle 文档中的单行函数和聚合函数。

PostgreSQL 用法

PostgreSQL 提供了广泛的单行和聚合函数列表。有些与它们的 Oracle 对应项相似（按名称和功能，或者使用不同的名称但具有相似的功能）。其他函数可以与其 Oracle 对应函数具有相同的名称，但表现出不同的功能。在下表中，“等效”列指示功能等效。

数值函数

Oracle 函数	函数定义	PostgreSQL 函数	函数定义	等效
`ABS`	n的绝对值：`abs (-11.3) → 11.3`。	`ABS(n)`	n的绝对值：`abs (-11.3) → 11.3`。	是的
`CEIL`	返回大于或等于 n 的最小整数：`ceil (-24.9) → -24`。	`CEIL` / `CEILING`	返回大于或等于 n 的最小整数：`ceil (-24.9) → -24`。	是的
`FLOOR`	返回等于或小于 n 的最大整数：`floor (-43.7) → -44`。	`FLOOR`	返回等于或小于 n 的最大整数：`floor (-43.7) → -44`。	是的
`MOD`	n1 除以 n2 的余数：`mod(10,3) → 1`。	`MOD`	n1 除以 n2 的余数：`mod(10,3) → 1`。	是的
`ROUND`	返回 n 四舍五入到小数点右侧的整数位：`round (3.49, 1) → 3.5`。	`ROUND`	返回 n 四舍五入到小数点右侧的整数位：`round (3.49, 1) → 3.5`。	是的
`TRUNC (Number)`	返回截断 n1 小数位的 n2：`trunc(13.5) → 13`。	`TRUNC (Number)`	返回截断 n1 小数位的 n2：`trunc(13.5) → 13`。	是的

字符函数

Oracle 函数	函数定义	PostgreSQL 函数	函数定义	等效
`CONCAT`	返回连接 char1 和 char2 的字符串：。`concat('a', 1) → a1`	`CONCAT`	连接所有参数的文本表示形式：`concat('a', 1) → a1`。	部分地
`LOWER` / `UPPER`	返回字符，所有字母小写或大写：`lower ('MR. Smith') → mr. smith`。	`LOWER` / `UPPER`	返回字符，所有字母小写或大写：`lower ('MR. Smith') → mr. smith`。	是的
`LPAD` / `RPAD`	返回 expr1，左填充或右填充到长度为 n 个字符，并在 expr2 中显示字符序列：`LPAD('Log-1',10,'-') → -----Log-1`。	`LPAD` / `RPAD`	返回 expr1，左填充或右填充到长度为 n 个字符，并在 expr2 中显示字符序列：`LPAD('Log-1',10,'-') → -----Log-1`。	是的
`REGEXP_REPLACE`	在字符串中搜索正则表达式模式：`regexp_replace('John', '[hn].', '1') → Jo1`。	`REGEXP_REPLACE`	替换与 POSIX 正则表达式匹配的子字符串：`regexp_replace('John', '[hn].', '1') → Jo1`。	是的
`REGEXP_SUBSTR`	通过在字符串中搜索正则表达式模式来扩展 SUBSTR 函数的功能：`REGEXP_SUBSTR('https://www.rockdata.-net/docs','https://(+\.?){3,4}/?') → https://www.rockdata.net/`。	`REGEXP_MATCHES` 或 `SUBSTRING`	返回通过将 POSIX 正则表达式与字符串匹配而产生的所有捕获的子字符串：`REGEXP_MATCHES ('https://www.rockdata.net/docs', '(https://+./)') → {https://www.rockdata.net/}` 或 `SUBSTRING ('https://www.rockdata.-net/docs', '(https://+./)') → https://www.rockdata.-net/`。	不
`REPLACE`	返回 char，每次出现的搜索字符串都替换为替换字符串：`replace ('abcdef', 'abc', '123') → 123def`。	`REPLACE`	返回 char，每次出现的搜索字符串都替换为替换字符串：`replace ('abcdef', 'abc', '123') → 123def`。	是的
`LTRIM` / `RTRIM`	从字符的左端或右端删除 set 中出现的所有字符：`ltrim ('zzzyrock', 'xyz') → rock`。	`LTRIM` / `RTRIM`	从字符串的开头删除仅包含字符的最长字符串（默认情况下为空格）：`ltrim('zzzyrock', 'xyz') → rock`。	是的
`SUBSTR`	指定开始字符位置，和子字符串的字符长度，返回一部分字符：`substr('John Smith', 6 ,1) → S`。	`SUBSTRING`	提取子字符串：`substring ( 'John Smith', 6 ,1) → S`。	不
`TRIM`	从字符串中剪裁前导字符或尾随字符（或两者）：`trim (both 'x' FROM 'xJohnxx') → John`。	`TRIM`	从字符串开头、结尾或两端尽最大可能删除指定字符串包含字符（默认情况下为空格）：`trim (both from 'yxJohnxx', 'xyz') → John`。	部分地
`ASCII`	返回 char 中第一个字符的数据库字符集的十进制表示形式：`ascii('a') → 97`。	`ASCII`	返回 char 中第一个字符的数据库字符集的十进制表示形式：`ascii('a') → 97`。	是的
`INSTR`	搜索子字符串的字符串	不适用	Oracle `INSTR`函数可以使用 PostgreSQL 内置函数进行模拟。	不
`LENGTH`	返回字符的长度：`length ('John S.') → 7`。	`LENGTH`	返回字符的长度：`length ('John S.') → 7`。	是的
`REGEXP_COUNT`	返回源字符串中出现模式的次数。	不适用		不
`REGEXP_INSTR`	在字符串位置搜索正则表达式模式。	不适用		不

日期时间函数

Oracle 函数	函数定义	PostgreSQL 函数	函数定义	等效
`ADD_MONTHS`	返回日期加上整数月份：`add_months( sysdate,1)`	不适用	PostgreSQL 可以使用`<date>+ interval month`语句实现相同的功能：`now () + interval '1 month'`。	不
`CURRENT_DATE`	返回会话时区中的当前日期：`select current_date from dual → 2017-01-01 13:01:01`。	`CURRENT_DATE`	PostgreSQL `CURRENT_DATE`会返回没有时间的日期，可以使用`now()`或`current_timestamp`函数来获得相同的结果：`select current_timestamp → 2017-01-01 13:01:01`。	部分地
`CURRENT_TIMESTAMP`	返回会话时区中的当前日期和时间：`select current_timestamp from dual; → 2017-01-01 13:01:01`。	`CURRENT_TIMESTAMP`	返回会话时区中的当前日期和时间：`select current_timestamp; → 2017-01-01 13:01:01`。	是的
`EXTRACT (date part)`	从日期时间或间隔表达式返回指定日期时间字段的值：`EXTRACT (YEAR FROM DATE '2017-03-07') → 2017`。	`EXTRACT (date part)`	从日期时间或间隔表达式返回指定日期时间字段的值：`EXTRACT (YEAR FROM DATE '2017-03-07') → 2017`。	是的
`LAST_DAY`	返回包含 date 的月份最后一天的日期：`LAST_DAY('05-07-2018') → 05-31-2018`。	不适用	如有必要，您可以使用 PostgreSQL 内置函数创建解决方法。	不
`BETWEEN`	返回日期 date1 和 date2 之间的月数：`MONTHS_BETWEEN ( sysdate, sysdate-100) → 3.25`。	不适用	作为替代解决方案，从PostgreSQL内置函数创建一个函数来实现相同的功能。没有十进制值的可能解决方案的示例：`DATE_PART ('month', now()) - DATE_PART('month', now()- interval'100 days') → 3`。	不
`SYSDATE`	返回为数据库服务器所在的操作系统设置的当前日期和时间：`select sysdate from dual; → 2017-01-01 13:01:01`。	`now()`	当前日期和时间，包括秒和时区的小数部分：`select now (); → 2017-01-01 13:01:01.123456+00`。	不
`SYSTIMESTAMP`	返回系统日期，包括秒的小数部分和时区：`select systimestamp from dual; → 2017-01-01 13:01:01.123456 PM+00:00`。	`NOW()`	当前日期和时间，包括秒和时区的小数部分：`select now (); → 2017-01-0113:01:01.123456+00`。	不
`LOCALTIMESTAMP`	以数据类型为 TIMESTAMP 的值返回会话时区中的当前日期和时间：`select localtimestamp from dual; → 01-JAN-17 10.01.10.123456 PM`。	`LOCALTIMESTAMP`	以数据类型为 TIMESTAMP 的值返回会话时区中的当前日期和时间：`select localtimestamp; → 01-JAN-17 10.01.10.123456 PM`。	是的
`TO_CHAR(datetime)`	将日期时间或时间戳转换为数据类型为 VARCHAR2 数据类型的值，其格式由日期格式指定：`to_char(sys-date, 'DD-MON-YYYY HH24:MI:SS'); → 01-JAN-2017 01:01:01`。	`TO_CHAR(datetime)`	将时间戳转换为字符串：`TO_CHAR(now(), 'DD-MONYYYY HH24:MI:SS'); → 01-JAN-2017 01:01:01`。	是的
`TRUNC (date)`	返回一个日期，其中一天的时间部分截断为格式模型指定的单位：`trunc(systimestamp); → 2017-01-01 00:00:00`。	`DATE_TRUNC`	截断为指定的精度：`date_trunc('day', now()); → 2017-01-01 00:00:00`。	不

编码和解码功能

Oracle 函数	函数定义	PostgreSQL 函数	函数定义	等效
`DECODE`	使用`IF-THEN-ELSE`语句的功能逐个比较表达式与每个搜索值。	`DECODE`	PostgreSQL 解码函数的行为与 Oracle 不同，PostgreSQL 从字符串中的文本表示中解码二进制数据，并且不具有`IF-THEN-ELSE`语句的功能。	不
`DUMP`	返回一个`VARCHAR2`值，其中包含数据类型代码、长度（以字节为单位）和表达式的内部表示形式。	不适用	不适用	不
`ORA_HASH`	计算给定表达式的哈希值。	不适用	不适用	不

空函数

Oracle 函数	函数定义	PostgreSQL 函数	函数定义	等效
`CASE`	`CASE`语句从一系列条件中进行选择，并运行相应的语句：`CASE WHEN condition THEN result [WHEN …] [ELSE result] END`。	`CASE`	PostgreSQL `CASE`表达式是一个通用条件表达式，类似于其他编程语言中的 if/else 语句：`CASE WHEN condition THEN result [WHEN …] [ELSE result] END`。	是的
`COALESCE`	返回表达式列表中的第一个非空表达式：`coalesce (null, 'a', 'b') → a`。	`COALESCE`	返回其第一个不为 null 的参数：`coalesce (null, 'a', 'b') → a`。	是的
`NULLIF`	比较 expr1 和 expr2。如果它们相等，则函数返回 null。如果它们不相等，该函数将返回 expr1：`NULLIF('a', 'b') → a`。	`NULLIF`	如果值 1 等于值 2，则返回空值，否则返回值 1：`NULLIF ('a', 'b') → a`。	是的
`NVL`	将 null（作为空白返回）替换为查询结果中的字符串：`NVL (null, 'a') → a`。	`COALESCE`	返回其第一个不为 null 的参数：`coalesce (null, 'a') → a`。	不
`NVL2`	根据指定的表达式是 null 还是不 null，确定查询返回的值。	不适用	可以改用`CASE`语句。	不

环境和标识符函数

Oracle 函数	函数定义	PostgreSQL 函数	函数定义	等效
`SYS_GUID`	生成并返回由 16 个字节组成的全局唯一标识符（RAW 值）：`select sys_guid() from dual → 5A280ABA8C76201EE0530-100007FF691`。	`UUID_GENERATE_V1()`	生成版本 1 UUID：`select uuid_generate_v1() → 90791a6-a359-11e7-a61c-12803bf1597a`。	不
`UID`	返回唯一标识会话用户（登录的用户）的整数：`select uid from dual → 84`	不适用	考虑使用 PostgreSQL current_user 函数和其他 PostgreSQL 内置函数来生成 UID。	不
`USER`	返回会话用户的名称：`select user from dual`。	`USER` / `SESSION_USER` / `CURRENT_USER` / `CURRENT_SCHEMA()`	当前运行上下文的用户名或模式：`select user;`或`select current_schema();`	不
`USERENV`	使用参数返回有关当前会话的信息：`SELECT USERENV ('LANGUAGE') "Language" FROM DUAL`	不适用	有关所有系统函数的列表，请参阅 PostgreSQL 文档。	不

转换函数

Oracle 函数	函数定义	PostgreSQL 函数	函数定义	等效
`CAST`	将一个内置数据类型或集合类型值转换为另一个内置数据类型或集合类型值：`cast ('10' as int) + 1 → 11`。	`CAST`	将一种数据类型转换为另一种数据类型：`cast ( '10' as int) + 1 → 11`。	是的
`CONVERT`	将字符串从一个字符集转换为另一个字符集：`select convert ('Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1') from dual`	不适用	不适用	不
`TO_CHAR (string / numeric)`	将`NCHAR`、`NVARCHAR2`、`CLOB`或`NCLOB`数据转换为数据库字符集：`select to_char ('01234') from dual → 01234`。	`TO_CHAR`	将第一个参数转换为第二个参数：`select to_char (01234, '00000') → 01234`。	不
`TO_DATE`	将`CHAR`、`VARCHAR2`、`NCHAR`或`NVARCHAR2`数据类型的字符转换为数据类型为`DATE`的值：`to_date('01Jan2017','DDMonYYYY') → 01-JAN-17`。	`TO_DATE`	将字符串转换为日期：`to_date('01Jan2017', 'DDMonYYYY') → 2017-01-01`。	部分地
`TO_NUMBER`	将 expr 转换为数据类型为`NUMBER`的值：`to_number('01234') → 1234` 或 `to_number('01234', '99999') → 1234`。	`TO_NUMBER`	将字符串转换为数字：`to_number('01234', '99999') → 1234`。	部分地

聚合函数

Oracle 函数	函数定义	PostgreSQL 函数	函数定义	等效
`AVG`	返回表达式的平均值：`select avg(salary) from employees`。	`AVG`	所有输入值的平均值（算术平均值）：`select avg(salary) from employees`。	是的
`COUNT`	返回查询返回的行数：`select count(*) from employees`。	`COUNT`	输入行数：`select count(*) from employees`。	是的
`LISTAGG`	对`ORDER BY`子句中指定的每个组内的数据进行排序，然后拼接度量列值的值：`select listagg(firstname,' ,') within group (order by customerid) from customer`。	`STRING_AGG`	将输入值拼接成字符串，用分隔符分隔：`select string_agg(firstname, ' ,') from customer order by 1;`。	不
`MAX`	返回表达式的最大值：`select max(salary) from employees`。	`MAX`	返回表达式的最大值：`select max(salary) from employees`。	是的
`MIN`	返回表达式的最小值：`select min(salary) from employees`。	`MIN`	返回表达式的最小值：`select min(salary) from employees`。	是的
`SUM`	返回表达式的值之和：`select sum(salary) from employees`。	`SUM`	返回表达式的值之和：`select sum(salary) from employees`。	是的

前 N 个查询 Oracle 12c

Oracle 功能	功能定义	PostgreSQL 功能	功能定义	等效
`FETCH`	从多行查询的结果集中检索数据行：`select * from customer fetch first 10 rows only`。	`FETCH`或`LIMIT`	仅检索由查询的其余部分生成的部分行：`select * from customer fetch first 10 rows only`。	是的

REGEXP_MATCH是 PostgreSQL 10 中引入的新模式匹配函数。

SELECT REGEXP_MATCH('foobarbequebaz','bar.*que');
regexp_match
-------------
{barbeque}

有关详细信息，请参阅 PostgreSQL 文档中的函数和运算符、数学函数和运算符、字符串函数和运算符和 uuid-ossp 函数。

Oracle 用法

PostgreSQL 用法

搜索

分类

标签