《PostgreSQL 开发指南》第 16 篇 常用函数（一）

文章转载自公众号：SQL编程思想

函数（function）是一些预定义好的代码模块，可以将输入进行计算和处理，最终输出一个结果值。

PostgreSQL 可以分为两类：标量函数（scalar function）和聚合函数（aggregation function）。标量函数针对每个输入都会返回相应的结果，聚合函数针对一组输入汇总出一个结果。在第 13 篇中已经介绍了几个常见的聚合函数。

本篇主要介绍 PostgreSQL 提供的标量函数。为了便于学习，可以将常见的系统函数分为以下类别：数学函数、字符函数、日期时间函数以及类型转换函数。

除了可以使用这些系统内置的函数之外，PostgreSQL 也支持创建自定义的函数（UDF）。

数学函数

数学函数和运算符用于执行算术运算，输入和输出通常都是数字类型。

算术运算符

PostgreSQL 支持以下算术运算符：

其中，按位运算只对整型数字类型有效；左移 N 位相当于乘以 2 的 N 次方，右移 N 位相当于除以 2 的 N 次方。

绝对值

abs(x) 函数用于计算 x 的绝对值。例如：

SELECT abs(-17.4);|  abs ||------|| 17.4 |

取整函数

ceil(dp)/ceiling(dp) 函数用于计算大于或等于 dp 的最小整数；floor(dp) 函数用于计算小于或等于 dp 的最大整数；round(dp) 函数四舍五入为整数；trunc(dp) 函数向零取整。

SELECT ceil(-42.8), floor(-42.8), round(12.45), trunc(12.8);| ceil | floor | round | trunc ||------|-------|-------|-------||  -42 |   -43 |    12 |    12 |

另外，round(dp, s) 函数四舍五入到 s 位小数；trunc(dp, s) 函数截断到 s 位小数。

乘方与开方

power(a, b) 函数计算 a 的 b 次方；sqrt(dp) 函数计算 dp 的平方根；cbrt(dp) 函数计算 dp 的立方根。

SELECT power(2, 3), sqrt(4), cbrt(27);| power | sqrt |               cbrt ||-------|------|--------------------||     8 |    2 | 3.0000000000000004 |

指数与对数

exp(dp) 函数计算以自然常数 e 为底的指数，ln(dp) 函数计算以自然常数 e 为底数的对数，log(dp)/log10(dp) 函数计算以 10 为底的对数，log(b, x) 函数计算以 b 为底的对数。

SELECT exp(1.0), ln(2.718281828459045), log(100), log(2.0, 16.0);|               exp |                 ln | log | log ||-------------------|--------------------|-----|-----|| 2.718281828459045 | 0.9999999999999999 |   2 |   4 |

整数商和余数

div(y, x) 函数计算 y 除以 x 的整数商，mod(y, x) 函数计算 y 除以 x 的余数。

SELECT div(9,4), mod(9,4);| div | mod ||-----|-----||   2 |   1 |

弧度与角度

degrees(dp) 函数用于将弧度转为角度，radians(dp) 函数用于将角度转弧度。

SELECT degrees(1.57), radians(90.0);|           degrees |            radians ||-------------------|--------------------|| 89.95437383553924 | 1.5707963267948966 |

常量 π

pi() 函数用于返回常量“π”的值。

SELECT pi();|                pi ||-------------------|| 3.141592653589793 |

符号函数

sign(dp) 返回参数的正负号，可能的结果为 -1、0、+1。

SELECT sign(-8.4);| sign ||------||   -1 |

生成随机数

PostgreSQL 提供了用于返回一个随机数的函数 random()。

SELECT random();|              random ||---------------------|| 0.07772749848663807 |

random() 返回一个大于等于 0 小于 1 的随机数，类型为双精度浮点数。

另外，setseed(dp) 函数可以为随后一次运行的 random() 函数设置种子数，参数范围：-1.0 <= dp <= 1.0。

SELECT setseed(0);SELECT random();|             random ||--------------------|| 0.8401877167634666 |

相同的种子可以得到相同的随机数，用于重现结果。

📝PostgreSQL 还提供了常用的三角函数和双曲函数，具体参考官方文档。

字符函数

字符函数和运算符用于处理字符串数据。

字符串连接

concat(str, …) 函数用于连接字符串，并且忽略其中的 NULL 参数；concat_ws(sep, str, …) 函数使用指定分隔符 sep 连接字符串。

SELECT concat(2, NULL, 22), concat_ws(' and ', 2, NULL, 22);| concat | concat_ws ||--------|-----------||    222 |  2 and 22 |

两个竖杠（||）也可以用于连接字符串，但是 NULL 参数将会返回 NULL。

SELECT 'Post'||'greSQL', 'Post'||NULL||'greSQL';|   ?column? | ?column? ||------------|----------|| PostgreSQL |   (null) |

字符与编码

ascii(string) 函数返回第一个字符的 ASCII 码。对于 UTF8 返回 Unicode 码；对于其他多字节编码，参数必须是一个 ASCII 字符。

SELECT ascii('x');| ascii ||-------||   120 |

chr(int) 函数返回编码对应的字符。对于 UTF8，参数指定的是 Unicode 码；对于其他多字节编码，参数必须对应一个 ASCII 字符。参数不允许为 0（空字符），因为 text 数据类型不能存储空字符。

SELECT chr(120);| chr ||-----||   x |

字符串长度

bit_length(string) 函数计算字符串包含的比特数；length(string)/char_length(string)/character_length(string) 函数计算字符串包含的字符数；octet_length(string) 函数计算字符串包含的字节数。

SELECT bit_length('jose'), length('jose'), octet_length('jose');| bit_length | length | octet_length ||------------|--------|--------------||         32 |      4 |            4 |

大小写转换

lower(string) 函数将字符串转换为小写形式，upper(string) 函数将字符串转换为大写形式，initcap(string) 函数将每个单词的首字母大写，其他字母小写。

SELECT lower('TOM'), upper('tom'), initcap('hi THOMAS');| lower | upper |   initcap ||-------|-------|-----------||   tom |   TOM | Hi Thomas |

子串查找与替换

substring(string [from] [for]) 函数用于提取从位置 from 开始的 for 个字符子串，位置从 1 开始计算。substr(string, from [, count]) 的作用相同。

SELECT substring('Thomas' from 2 for 3), substr('Thomas',2, 3);| substring | substr ||-----------|--------||       hom |    hom |

left(str, n) 函数返回字符串左边的 n 个字符。如果 n 为负数，返回除了最后 |n| 个字符之外的所有字符。

right(str, n) 函数返回字符串右边的 n 个字符。如果 n 为负数，返回除了左边 |n| 个字符之外的字符。

SELECT left('abcde', 2), right('abcde', 2);| left | right ||------|-------||   ab |    de |

substring(string from pattern) 函数提取匹配 POSIX 正则表达式的子串。

substring(string from pattern for escape) 函数提取匹配 SQL 正则表达式的子串。

SELECT substring('Thomas' from '...$'), substring('Thomas' from '%#"o_a#"_' for '#');| substring | substring ||-----------|-----------||       mas |       oma |

regexp_match(string, pattern [, flags]) 函数返回匹配 POSIX 正则表达式的第一个子串。

SELECT regexp_match('foobarbequebaz', '(bar)(beque)');|regexp_match||------------||{bar,beque} |

regexp_matches(string, pattern [, flags]) 函数返回匹配 POSIX 正则表达式的所有子串，结果是一个集合。

SELECT regexp_matches('foobarbequebaz', 'ba.', 'g');| regexp_matches ||----------------||            bar ||            baz |

position(substring in string) 返回子串的位置；strpos(string, substring) 函数的作用相同，但是参数顺序相反。

SELECT position('om' in 'Thomas'), strpos('Thomas', 'om');| position | strpos ||----------|--------||        3 |      3 |

starts_with(string, prefix) 函数判断 string 是否以 prefix 开头，如果是则返回 true；否则返回 false。

SELECT starts_with('alphabet', 'alph');|starts_with||-----------||true       |

replace(string, from, to) 函数将字符串 string 中的 from 子串替换为 to 子串；regexp_replace(string, pattern, replacement [, flags]) 函数字符串 string 中匹配 POSIX 正则表达式 pattern 的子串替换为 replacement。

SELECT replace('abcdefabcdef', 'cd', 'XX'), regexp_replace('Thomas', '.[mN]a.', 'M');|      replace | regexp_replace ||--------------|----------------|| abXXefabXXef |            ThM |

translate(string , from , to) 函数将字符串 string 中出现在 from 中的字符串替换成 to 中相应位置的字符。如果 from 长度大于 to，在 to 中没有对应值的字符将被删除。

SELECT translate('12345', '143', 'ax');| translate ||-----------||      a2x5 |

overlay(string placing substring from [for]) 函数使用 substring 覆盖字符串 string 中从 from 开始的 for 个字符。

SELECT overlay('Txxxxas' placing 'hom' from 2 for 4);| overlay ||---------||  Thomas |

截断与填充

trim([leading | trailing | both] [characters] from string) 函数从字符串的开头（leading）、结尾（trailing）或者两端（both）删除由指定字符 characters（默认为空格）组成的最长子串；trim([leading | trailing | both] [from] string [, characters]) 函数的作用相同。

SELECT trim(both 'xyz' from 'yxTomxx');| btrim ||-------||   Tom |

btrim(string [, characters]) 函数的作用与上面 trim 函数的 both 选项相同；ltrim(string [, characters]) 与上面 trim 函数的 leading 选项相同；rtrim(string [, characters]) 函数上面 trim 函数的 trailing 选项相同。

SELECT btrim('yxTomxx', 'xyz'), ltrim('yxTomxx', 'xyz'), rtrim('yxTomxx', 'xyz');| btrim | ltrim | rtrim ||-------|-------|-------||   Tom | Tomxx | yxTom |

lpad(string, length [, fill ]) 函数在 string 左侧使用 fill 中的字符（默认空格）进行填充，直到长度为 length。如果 string 长度大于 length，从右侧截断到长度 length。

rpad(string, length [, fill ]) 函数在 string 右侧使用 fill 中的字符（默认空格）进行填充，直到长度为 length。如果 string 长度大于 length，从右侧截断到长度 length。

repeat(string, number) 函数将字符串 string 重复 N 次。

SELECT lpad('hi', 5, 'xy'), rpad('hi', 5, 'xy'), repeat('Pg', 4);|  lpad |  rpad |   repeat ||-------|-------|----------|| xyxhi | hixyx | PgPgPgPg |

字符串格式化

format(formatstr , formatarg) 用于对字符串格式化，类似于 C 语言中的 sprintf 函数。

SELECT format('Hello %s, %1$s', 'World');|             format ||--------------------|| Hello World, World |

📝关于 format 函数的格式化参数可以参考官方文档。

MD5 值

md5(string) 函数用于返回十六进制格式的 MD5 值。

SELECT md5('abc');|                              md5 ||----------------------------------|| 900150983cd24fb0d6963f7d28e17f72 |

字符串拆分

regexp_split_to_table(string, pattern[, flags]) 函数用于拆分字符串，使用 POSIX 正则表达式作为分隔符。函数的返回类型是 text 集合。

SELECT regexp_split_to_table('hello world', '\s+');| regexp_split_to_table ||-----------------------|

|                 hello |

|                 world |

split_part(string, delimiter, field) 函数使用 delimiter 拆分字符串，并返回指定项（从 1 开始计数）。

SELECT split_part('abc~@~def~@~ghi', '~@~', 2);

| split_part |

|------------|

|        def |

字符串反转

reverse(str) 函数用于将字符串反转。

SELECT reverse('上海自来水');

| reverse |

|---------|

|   水来自海上 |

📝更多字符函数可以参考官方文档。

下一篇我们继续介绍 PostgreSQL 中的常用日期时间函数和类型转换函数。

中国PostgreSQL分会与腾讯云战略合作协议签订

PostgreSQL 14.0 正式发布

深度报告：开源协议那些事儿

从“非主流”到“潮流”，开源早已值得拥有

Oracle中国正在进行新一轮裁员，传 N+6 补偿

PostgreSQL与MySQL版权比较

新闻|Babelfish使PostgreSQL直接兼容SQL Server应用程序

四年三冠，PostgreSQL再度荣获“年度数据库”

中国PostgreSQL分会入选工信部重点领域人才能力评价机构

更多新闻资讯，行业动态，技术热点，请关注中国PostgreSQL分会官方网站

https://www.postgresqlchina.com

中国PostgreSQL分会生态产品

https://www.pgfans.cn

中国PostgreSQL分会资源下载站

https://www.postgreshub.cn