前言
写在前面，当我们在用PostgreSQL数据库进行数据取数时，难免会遇到一些数据处理上的场景，本文会针对一些常用的内置函数进行总结。
-- 查看postgresql的版本(以下任一语句都可以实现)
select version();
show server_version;
1.格式转换
1.1 格式转换符显示转换
利用双冒号可以直接进行格式转换，语法如下：
字段名或数值::数据类型
例子如下：
-- 将文本'123'转为int8类型
SELECT
'123' :: int8 num;
-- 将文本类型字段t1转为int8类型
SELECT
t1 :: int8
from temp;
1.2 利用数据类型显示转换
利用数据类型进行转换，语法如下：
数据类型数值
例子：
-- 将文本'123'转为int8类型
SELECT
int8'123' num;
1.3 格式转换函数显示转换
利用数据转换函数cast进行转换，语法如下：
cast(字段名或数值 as 数据类型)
例子：
-- 将文本'123'转为int8类型
SELECT
cast('123' as int4) num;
-- 将文本字段t1转为int类型
SELECT CAST( t1 AS INT ) t1_c
FROM
TEMP;1.4 转换案例
-- 文本转整数
SELECT CAST
( '123' AS int4 );
-- 文本转浮点数字
SELECT CAST
( '123.34' AS DECIMAL );
SELECT CAST
( '123.34' AS NUMERIC );
-- 数字转文本
SELECT CAST
( 123 AS VARCHAR );--可变字符串
SELECT CAST
( - 123 AS CHAR ( 2 ) );-- 固定字符串，进行截断，将-123转为'-1'
SELECT CAST
( - 123 AS CHAR ( 6 ) );-- 固定字符串，进行空格填充，将-123转为'-123 '
SELECT CAST
( 124.94 AS TEXT );--可变字符串,将124.94转为'124.94'
SELECT
to_char( 124.94, '999D9' );--将124.94转为'124.9',遵循四舍五入
SELECT
to_char( 124.94, 'FM999.99' );--将124.94转为'124.94'
SELECT
to_char( - 124.94, 'FM9999999.99' );--将-124.94转为'-124.94'
SELECT
to_char( - 124.94, 'FM9999999.990' );--将-124.94转为'-124.940'
SELECT
to_char( 124, '00000' );--左端用零补齐凑够5位，将124转为'00124'
SELECT
to_char( 124, '99999' );--左端用空格补齐凑够5位，将124转为' 124'
SELECT
to_char( - 124.945, 'FM999' );--只显示整数部分，遵循四舍五入
-- 时间戳(timestamp)转日期(date)
SELECT CAST
( now( ) AS DATE );--普通日期模式
-- 时间戳(timestamp)转文本
SELECT CAST
( now( ) AS TEXT );--不指定输出格式
SELECT
to_char( now( ), 'yyyy-mm-dd' );--指定输出格式;
-- 文本转日期(date)
SELECT
to_date( '2012-01-01', 'yyyy-mm-dd' );
-- 文本转时间戳(TIMESTAMP)
SELECT
to_timestamp( '2012-01-01 12:02:01', 'yyyy-mm-dd HH24:MI:SS' );
2.数学计算
2.1 数学运算操作符
| 操作符 | 描述 | 例子 | 结果 |
|---|---|---|---|
| + | 加 | 2 + 3 | 5 |
| - | 减 | 2 - 3 | -1 |
| * | 乘 | 2 * 3 | 6 |
| / | 除（整数除法截断结果） | 4 / 2 | 2 |
| % | 模（取余） | 5 % 4 | 1 |
| ^ | 指数（从左至右结合） | 2.0 ^ 3.0 | 8 |
| |/ | 平方根 | |/ 25.0 | 5 |
| ||/ | 立方根 | ||/ 27.0 | 3 |
| ! | 阶乘 | 5 ! | 120 |
| !! | 阶乘（前缀操作符） | !! 5 | 120 |
| @ | 绝对值 | @ -5.0 | 5 |
| & | 按位与 | 91 & 15 | 11 |
| | | 按位或 | 32 | 3 |
| # | 按位异或 | 17 # 5 | 20 |
| ~ | 按位求反 | ~1 | -2 |
| << | 按位左移 | 1 << 4 | 16 |
| >> | 按位右移 | 8 >> 2 | 2 |
2.2 数学运算函数
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
| abs(x) | 和输入相同 | 绝对值 | abs(-12.43) | 12.43 |
| cbrt(dp) | double | 立方根 | cbrt(27.0) | 3 |
| ceil(dp or numeric) | 和输入相同 | 不小于参数的最近的整数 | ceil(-42.8) | -42 |
| ceiling(dp or numeric) | 和输入相同 | 不小于参数的最近的整数（ceil的别名） | ceiling(-95.3) | -95 |
| degrees(dp) | dp | 把弧度转为角度 | degrees(0.5) | 28.6478897565412 |
| div(y numeric, x numeric) | numeric | y/x的整数商 | div(9,4) | 2 |
| exp(dp or numeric) | 和输入相同 | 指数 | exp(1.0) | 2.71828182845905 |
| floor(dp or numeric) | 和输入相同 | 不大于参数的最近的整数 | floor(-42.8) | -43 |
| ln(dp or numeric) | 和输入相同 | 自然对数 | ln(2.0) | 0.693147180559945 |
| log(dp or numeric) | 和输入相同 | 以10为底的对数 | log(100.0) | 2 |
| log10(dp or numeric) | 和输入相同 | 以10为底的对数 | log10(100.0) | 2 |
| log(b numeric, x numeric) | numeric | 以b为底的对数 | log(2.0, 64.0) | 6.0000000000 |
| mod(y, x) | 和参数类型相同 | y/x的余数 | mod(9,4) | 1 |
| pi() | dp | “π”常数 | pi() | 3.14159265358979 |
| power(a dp, b dp) | dp | 求a的b次幂 | power(9.0, 3.0) | 729 |
| power(a numeric, b numeric) | numeric | 求a的b次幂 | power(9.0, 3.0) | 729 |
| radians(dp) | dp | 把角度转为弧度 | radians(45.0) | 0.785398163397448 |
| round(dp or numeric) | 和输入相同 | 圆整为最接近的整数 | round(42.4) | 42 |
| round(v numeric, s int) | numeric | 圆整为s位小数数字 | round(42.4382, 2) | 42.44 |
| scale(numeric) | integer | 参数的精度（小数点后的位数） | scale(8.41) | 2 |
| sign(dp or numeric) | 和输入相同 | 参数的符号（-1, 0, +1） | sign(-8.4) | -1 |
| sqrt(dp or numeric) | 和输入相同 | 平方根 | sqrt(2.0) | 1.4142135623731 |
| trunc(dp or numeric) | 和输入相同 | 截断（向零靠近） | trunc(42.8) | 42 |
| trunc(v numeric, s int) | numeric | 截断为s位小数位置的数字 | trunc(42.4382, 2) | 42.43 |
3.逻辑计算
3.1 逻辑操作符
postgresql中的逻辑操作符，有以下三种：
- AND
- OR
- NOT
3.2 比较操作符
| 操作符 | 描述 |
|---|---|
| < | 小于 |
| > | 大于 |
| <= | 小于等于 |
| >= | 大于等于 |
| = | 等于 |
| <> or != | 不等于 |
!=操作符在分析器阶段被转换成<>
3.3 比较谓词
| 谓词 | 描述 |
|---|---|
| a BETWEEN x AND y | 在x和y之间 |
| a NOT BETWEEN x AND y | 不在x和y之间 |
| a BETWEEN SYMMETRIC x AND y | 在对比较值排序后位于x和y之间 |
| a NOT BETWEEN SYMMETRIC x AND y | 在对比较值排序后不位于x和y之间 |
| a IS DISTINCT FROM b | 不等于，空值被当做一个普通值 |
| a IS NOT DISTINCT FROM b | 等于，空值被当做一个普通值 |
| expression IS NULL | 是空值 |
| expression IS NOT NULL | 不是空值 |
| expression ISNULL | 是空值（非标准语法） |
| expression NOTNULL | 不是空值（非标准语法） |
| boolean_expression IS TRUE | 为真 |
| boolean_expression IS NOT TRUE | 为假或未知 |
| boolean_expression IS FALSE | 为假 |
| boolean_expression IS NOT FALSE | 为真或者未知 |
| boolean_expression IS UNKNOWN | 值为未知 |
| boolean_expression IS NOT UNKNOWN | 为真或者为假 |
3.4 比较函数
| 函数 | 描述 | 例子 | 例子结果 |
|---|---|---|---|
| num_nonnulls(VARIADIC “any”) | 返回非空参数的数量 | num_nonnulls(0, NULL, 1 ,2 ,3) | 4 |
| num_nulls(VARIADIC “any”) | 返回空参数的数量 | num_nulls(0, NULL, 1 ,2 ,3) | 1 |
4.字符串及相关匹配函数
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
| string || string | text | 串接 | ‘Hello’ || ‘Word’ | ‘HelloWord’ |
| string || non-string or non-string || string | text | 使用一个非字符串输入的串接 | 'Value: ’ || 42 | Value: 42 |
| bit_length(string) | int | 串中的位数 | bit_length(‘Hello’) | 40 |
| char_length(string) or character_length(string) | int | 串中字符数 | char_length(‘Hello’) | 4 |
| lower(string) | text | 将字符串转换为小写形式 | lower(‘Hello’) | hello |
| overlay(string placing string from int [for int]) | text | 替换子串,for后面是指替换的位数 | overlay(‘Hexxx,word’ placing ‘llo’ from 3 for 4) | Helloword |
| position(substring in string) | int | 定位指定子串位置，可利用值是否大于0来判断是否包含子串 | position(‘lo’ in ‘hello’) | 4 |
| substring(string [from int] [for int]) | text | 提取子串 | substring(‘hello’ from 1 for 3) | hel |
| substring(string from pattern) | text | 提取匹配POSIX正则表达式的子串 | substring(‘hello’ from ‘^…’) | hel |
| substr(string, from [, count]) | text | 提取子串 | substr(‘Hello’, 1, 3) | hel |
| trim([leading | trailing | both] [characters] from string) | text | 从string的开头、结尾或者两端（both是默认值）移除只包含characters（默认是一个空格）中字符的最长字符串 | trim(both ‘Hes’ from ‘sHehelloeHs’) | hello |
| trim([leading | trailing | both] [from] string [, characters] ) | text | trim()的非标准版本 | trim(both from ‘hhHellohh’, ‘h’) 或trim(‘hhHellohh’, ‘h’) | Tom |
| upper(string) | text | 将字符串转换成大写形式 | upper(‘hello’) | HELLO |
| concat(str “any” [, str “any” [, …] ]) | text | 串接所有参数的文本表示。NULL 参数被忽略。 | concat(‘abcde’, 2, NULL, 22) | abcde222 |
| concat_ws(sep text, str “any” [, str “any” [, …] ]) | text | 将除了第一个参数外的其他参数用分隔符串接在一起。第一个参数被用作分隔符字符串。NULL 参数被忽略。 | concat_ws(‘,’, ‘abcde’, 2, NULL, 22) | abcde,2,22 |
| left(str text, n int) | text | 返回字符串中的前n个字符。当n为负时，将返回除了最后|n|个字符之外的所有字符。 | left(‘abcde’, 2) | ab |
| length(string) | int | string中的字符数 | length(‘hello’) | 5 |
| length(string bytea, encoding name ) | int | string在给定编码中的字符数。string必须在这个编码中有效。 | length(‘hello’, ‘UTF8’) | 5 |
| lpad(string text, length int [, fill text]) | text | 将string通过前置字符fill（默认是一个空格）填充到长度length。如果string已经长于length，则它被（从右边）截断。 | lpad(‘hi’, 5, ‘ab’) | abahi |
| ltrim(string text [, characters text]) | text | 从string的开头删除最长的只包含characters（默认是一个空格）的串 | ltrim(‘zzzytest’, ‘xyz’) | test |
| regexp_match(string text, pattern text [, flags text]) | text[] | 返回一个POSIX正则表达式与string的第一个匹配得到的子串。 | regexp_match(‘foobarbequetarz’, ‘(foo)(bar)’) | 一行：{foo,bar} |
| regexp_matches(string text, pattern text [, flags text]) | setof text[] | 返回一个POSIX正则表达式与string匹配得到的子串 | regexp_matches(‘foobarbequetarz’, ‘.ar’, ‘g’) | 两行：{bar} {tar} |
| regexp_replace(string text, pattern text, replacement text [, flags text]) | text | 替换匹配一个POSIX正则表达式的子串。 | regexp_replace(‘Hello’, ‘l+.’, ‘r’) | Her |
| regexp_split_to_array(string text, pattern text [, flags text ]) | text[] | 使用一个POSIX正则表达式作为分隔符划分string。 | regexp_split_to_array(‘hello world’, ‘\s+’) | 一行：{hello,world} |
| regexp_split_to_table(string text, pattern text [, flags text]) setof | text | 使用一个POSIX正则表达式作为分隔符划分string。 | regexp_split_to_table(‘hello world’, ‘\s+’) | 两行：hello world |
| repeat(string text, number int) | text | 重复string指定的number次 | repeat(‘he’, 3) | hehehe |
| replace(string text, from text, to text) | text | 将string中出现的所有子串from替换为子串to | replace(‘hello’, ‘ello’, ‘is’) | his |
| reverse(str) | text | 返回反转的字符串 | reverse(‘abcde’) | edcba |
| right(str text, n int) | text | 返回字符串中的最后n个字符。如果n为负，返回除最前面的|n|个字符外的所有字符。 | right(‘abcde’, 2) | de |
| rpad(string text, length int [, fill text]) | text | 将string通过增加字符fill（默认为一个空格）填充到长度length。如果string已经长于length则它会被截断。 | rpad(‘hi’, 5, ‘xy’) | hixyx |
| rtrim(string text [, characters text]) | text | 从string的结尾删除最长的只包含characters（默认是一个空格）的串 | rtrim(‘testxxzx’, ‘xyz’) | test |
| split_part(string text, delimiter text, field int) | text | 按delimiter划分string并返回给定域（从1开始计算） | split_part(‘you!hello!world!’, ‘!’, 2) | hello |
| strpos(string, substring) | int | 指定子串的位置（和position(substring in string)相同，但是注意相反的参数顺序） | strpos(‘hello’, ‘o’) | 5 |
| starts_with(string, prefix) | bool | 如果string以prefix开始则返回真。 | starts_with(‘alphabet’, ‘alph’) | t |
5.时间与日期函数
5.1时间类操作符
| 操作符 | 例子 | 结果 |
|---|---|---|
| + | date ‘2001-09-28’ + integer ‘7’ | date ‘2001-10-05’ |
| + | date ‘2001-09-28’ + interval ‘1 hour’ | timestamp ‘2001-09-28 01:00:00’ |
| + | date ‘2001-09-28’ + time ‘03:00’ | timestamp ‘2001-09-28 03:00:00’ |
| + | interval ‘1 day’ + interval ‘1 hour’ | interval ‘1 day 01:00:00’ |
| + | timestamp ‘2001-09-28 01:00’ + interval ‘23 hours’ | timestamp ‘2001-09-29 00:00:00’ |
| + | time ‘01:00’ + interval ‘3 hours’ | time ‘04:00:00’ |
| - | - interval ‘23 hours’ | interval ‘-23:00:00’ |
| - | date ‘2001-10-01’ - date ‘2001-09-28’ | integer ‘3’ (days) |
| - | date ‘2001-10-01’ - integer ‘7’ | date ‘2001-09-24’ |
| - | date ‘2001-09-28’ - interval ‘1 hour’ | timestamp ‘2001-09-27 23:00:00’ |
| - | time ‘05:00’ - time ‘03:00’ | interval ‘02:00:00’ |
| - | time ‘05:00’ - interval ‘2 hours’ | time ‘03:00:00’ |
| - | timestamp ‘2001-09-28 23:00’ - interval ‘23 hours’ | timestamp ‘2001-09-28 00:00:00’ |
| - | interval ‘1 day’ - interval ‘1 hour’ | interval ‘1 day -01:00:00’ |
| - | timestamp ‘2001-09-29 03:00’ - timestamp ‘2001-09-27 12:00’ | interval ‘1 day 15:00:00’ |
| * | 900 * interval ‘1 second’ | interval ‘00:15:00’ |
| * | 21 * interval ‘1 day’ | interval ‘21 days’ |
| * | double precision ‘3.5’ * interval ‘1 hour’ | interval ‘03:30:00’ |
| / | interval ‘1 hour’ / double precision ‘1.5’ | interval ‘00:40:00’ |
5.2 时间、日期类函数
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
| age(timestamp, timestamp) | interval | 减去参数，生成一个使用年、月（而不是只用日）的“符号化”的结果 | age(timestamp ‘2001-04-10’, timestamp ‘1957-06-13’) | 43 years 9 mons 27 days |
| age(timestamp) | interval | 从current_date（在午夜）减去 | age(timestamp ‘1957-06-13’)，假如今日为2022-06-14 | 65 years 1 day |
| clock_timestamp() | timestamp with time zone | 当前日期和时间（在语句执行期间变化） | clock_timestamp() | 2022-06-14 19:06:54.034672+08 |
| current_date | date | 当前日期 | current_date | 2022-06-14 |
| current_time | time with time zone | 当前时间（一天中的时间），带时区 | current_time | 19:11:04.336139+08 |
| current_timestamp | timestamp with time zone | 当前日期和时间（当前事务开始时），带时区 | current_timestamp | 2022-06-14 19:11:57.83455+08 |
| date_part(text, timestamp) | double precision | 获得子域（等价于extract） | date_part(‘hour’, timestamp ‘2022-06-14 20:38:40’) | 20 |
| date_part(text, interval) | double precision | 获得子域（等价于extract） | date_part(‘month’, interval ‘2 years 3 months’) | 3 |
| date_trunc(text, timestamp) | timestamp | 截断到指定精度 | date_trunc(‘hour’, timestamp ‘2022-06-14 20:38:40’) | 2022-06-14 20:00:00 |
| date_trunc(text, timestamp with time zone, text) | timestamp with time zone | 在指定的时区截断到指定的精度 | ddate_trunc(‘day’, timestamptz ‘2022-06-14 20:38:40+00’, ‘Australia/Sydney’) | 2022-06-14 22:00:00+08 |
| date_trunc(text, interval) | interval | 截断到指定精度 | date_trunc(‘hour’, interval ‘2 days 3 hours 40 minutes’) | 2 days 03:00:00 |
| extract(field from timestamp) | double precision | 获得子域 | extract(MINUTE from timestamp ‘2022-06-14 20:38:40’) | 38 |
| extract(field from interval) | double precision | 获得子域 | extract(month from interval ‘2 years 3 months’) | 3 |
| isfinite(date) | boolean | 测试有限日期（不是+/-无限） | isfinite(date ‘2022-06-14’) | true（实际缩写为t） |
| isfinite(timestamp) | boolean | 测试有限时间戳（不是+/-无限） | isfinite(timestamp ‘2022-06-14 21:28:30’) | true（实际缩写为t） |
| isfinite(interval) | boolean | 测试有限间隔 | isfinite(interval ‘2 minutes’) | true（实际缩写为t） |
| justify_days(interval) | interval | 调整间隔这样30天时间周期可以表示为月 | justify_days(interval ‘35 days’) | 1 mon 5 days |
| justify_hours(interval) | interval | 调整间隔这样24小时时间周期可以表示为日 | justify_hours(interval ‘27 hours’) | 1 day 03:00:00 |
| justify_interval(interval) | interval | 使用justify_days和justify_hours调整间隔，使用额外的符号调整 | justify_interval(interval ‘1 mon -1 hour’) | 29 days 23:00:00 |
| localtime | time | 当前时间（一天中的时间），不带时区 | localtime | 19:21:14.958286 |
| localtimestamp | timestamp | 当前日期和时间（当前事务的开始），不带时区 | LOCALTIMESTAMP | 2022-07-22 19:23:54.073462 |
| make_date(year int, month int, day int) | date | 从年、月、日域创建日期 | make_date(2022, 7, 15) | 2022-07-15 |
| make_interval(years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0) | interval | 从年、月、周、日、时、分、秒域创建interval | make_interval(days => 10) | 10 days |
| make_time(hour int, min int, sec double precision) | time | 从时、分、秒域创建时间 | make_time(8, 15, 23.5) | 08:15:23.5 |
| make_timestamp(year int, month int, day int, hour int, min int, sec double precision) | timestamp | 从年、月、日、时、分、秒域创建时间戳 | make_timestamp(2013, 7, 15, 8, 15, 23.5) | 2013-07-15 08:15:23.5 |
| make_timestamptz(year int, month int, day int, hour int, min int, sec double precision, [ timezone text ]) | timestamp with time zone | 从年、月、日、时、分、秒域创建带时区的时间戳。如果没有指定timezone， 则使用当前时区。 | make_timestamptz(2022, 6, 14, 19, 30, 50.5) | 2022-06-14 19:30:50.5+08 |
| now() | timestamp with time zone | 当前日期和时间（当前事务的开始），带时区 | now() | 2022-07-22 19:28:15.804042+08 |
| statement_timestamp() | timestamp with time zone | 当前日期和时间（当前语句的开始），在一个事务的第一条命令期间返回值与CURRENT_TIMESTAMP相同 | statement_timestamp() | 2022-07-22 19:31:35.75589+08 |
| timeofday() | text | 当前日期和时间（像clock_timestamp，但是作为一个text字符串） | timeofday() | Fri Jul 22 19:35:19.000959 2022 CST |
| transaction_timestamp() | timestamp with time zone | 当前日期和时间（当前事务的开始）；等同于CURRENT_TIMESTAMP | transaction_timestamp() | 2022-07-22 19:34:02.369665+08 |
| to_timestamp(double precision) | timestamp with time zone | 把 Unix 时间（从 1970-01-01 00:00:00+00 开始的秒）转换成 timestamp | to_timestamp(1655211000) | 2022-06-14 20:50:00+08 |
6.数组函数
6.1 数组操作符
| 操作符 | 描述 | 例子 | 结果 |
|---|---|---|---|
| = | 等于 | ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] | t |
| <> | 不等于 | ARRAY[1,2,3] <> ARRAY[1,2,4] | t |
| < | 小于 | ARRAY[1,2,3] < ARRAY[1,2,4] | t |
| > | 大于 | ARRAY[1,4,3] > ARRAY[1,2,4] | t |
| <= | 小于等于 | ARRAY[1,2,3] <= ARRAY[1,2,3] | t |
| >= | 大于等于 | ARRAY[1,4,3] >= ARRAY[1,4,3] | t |
| @> | 包含 | ARRAY[1,4,3] @> ARRAY[3,1,3] | t |
| <@ | 被包含 | ARRAY[2,2,7] <@ ARRAY[1,7,4,2,6] | t |
| && | 重叠（具有公共元素） | ARRAY[1,4,3] && ARRAY[2,1] | t |
| || | 数组和数组串接 | ARRAY[1,2,3] || ARRAY[4,5,6] | {1,2,3,4,5,6} |
| || | 数组和数组串接 | ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]] | {{1,2,3},{4,5,6},{7,8,9}} |
| || | 元素到数组串接 | 3 || ARRAY[4,5,6] | {3,4,5,6} |
| || | 数组到元素串接 | ARRAY[4,5,6] || 7 | {4,5,6,7} |
6.2 数组函数
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
| array_append(anyarray, anyelement) | anyarray | 向一个数组的末端追加一个元素 | array_append(ARRAY[1,2], 3) | {1,2,3} |
| array_cat(anyarray, anyarray) | anyarray | 连接两个数组 | array_cat(ARRAY[1,2,3], ARRAY[4,5]) | {1,2,3,4,5} |
| array_ndims(anyarray) | int | 返回数组的维度数 | array_ndims(ARRAY[[1,2,3], [4,5,6]]) | 2 |
| array_dims(anyarray) | text | 返回数组的维度的文本表示 | array_dims(ARRAY[[1,2,3], [4,5,6]]) | [1:2][1:3] |
| array_fill(anyelement, int[], [, int[]]) | anyarray | 返回一个用提供的值和维度初始化好的数组，可以选择下界不为1 | array_fill(6, ARRAY[3], ARRAY[5]) | [5:7]={6,6,6} |
| array_length(anyarray, int) | int | 返回被请求的数组维度的长度 | array_length(array[1,2,3], 1) | 3 |
| array_lower(anyarray, int) | int | 返回被请求的数组维度的下界 | array_lower(‘[0:2]={1,2,3}’::int[], 1) | 0 |
| array_position(anyarray, anyelement [, int]) | int | 返回在该数组中从第三个参数指定的元素开始或者第一个元素开始（数组必须是一维的）、第二个参数的第一次出现的下标 | array_position(ARRAY[‘sun’,‘mon’,‘tue’,‘wed’,‘thu’,‘fri’,‘sat’], ‘mon’) | 2 |
| array_positions(anyarray, anyelement) | int[] | 返回在第一个参数给定的数组（数组必须是一维的）中，第二个参数所有出现位置的下标组成的数组 | array_positions(ARRAY[‘A’,‘A’,‘B’,‘A’], ‘A’) | {1,2,4} |
| array_prepend(anyelement, anyarray) | anyarray | 向一个数组的首部追加一个元素 | array_prepend(1, ARRAY[2,3]) | {1,2,3} |
| array_remove(anyarray, anyelement) | anyarray | 从数组中移除所有等于给定值的所有元素（数组必须是一维的） | array_remove(ARRAY[1,2,3,2], 2) | {1,3} |
| array_replace(anyarray, anyelement, anyelement) | anyarray | 将每一个等于给定值的数组元素替换成一个新值 | array_replace(ARRAY[1,2,5,4], 5, 3) | {1,2,3,4} |
| array_to_string(anyarray, text [, text]) | text | 使用提供的定界符和可选的空串连接数组元素 | array_to_string(ARRAY[1, 2, 3, NULL, 5], ‘,’, ‘*’) | ‘1,2,3,*,5’ |
| array_upper(anyarray, int) | int | 返回被请求的数组维度的上界 | array_upper(ARRAY[1,8,3,7], 1) | 4 |
| cardinality(anyarray) | int | 返回数组中元素的总数，如果数组为空则返回0 | cardinality(ARRAY[[1,2],[3,4]]) | 4 |
| string_to_array(text, text [, text]) | text[] | 使用提供的定界符和可选的空串将字符串划分成数组元素 | string_to_array(‘a-b-c-d-e-g-’, ‘-’, ‘’) | {a,b,c,d,e,g,NULL} |
| unnest(anyarray) | setof anyelement | 将一个数组扩展成一组行 | unnest(ARRAY[1,2]) | 2行：1 2 |
7.范围函数
7.1 范围操作符
| 操作符 | 描述 | 例子 | 结果 |
|---|---|---|---|
| = | 等于 | int4range(1,5) = ‘[1,4]’::int4range | t |
| <> | 不等于 | numrange(1.1,2.2) <> numrange(1.1,2.3) | t |
| < | 小于 | int4range(1,10) < int4range(2,3) | t |
| > | 大于 | int4range(1,10) > int4range(1,5) | t |
| <= | 小于等于 | numrange(1.1,2.2) <= numrange(1.1,2.2) | t |
| >= | 大于等于 | numrange(1.1,2.2) >= numrange(1.1,2.0) | t |
| @> | 包含范围 | int4range(2,4) @> int4range(2,3) | t |
| @> | 包含元素 | ‘[2011-01-01,2011-03-01)’::tsrange @> ‘2011-01-10’::timestamp | t |
| <@ | 范围被包含 | int4range(2,4) <@ int4range(1,7) | t |
| <@ | 元素被包含 | 42 <@ int4range(1,7) | f |
| && | 重叠（有公共点） | int8range(3,7) && int8range(4,12) | t |
| << | 严格左部 | int8range(1,10) << int8range(100,110) | t |
| >> | 严格右部 | int8range(50,60) >> int8range(20,30) | t |
| &< | 不超过右部 | int8range(1,20) &< int8range(18,20) t | |
| &> | 不超过左部 | int8range(7,20) &> int8range(5,10) | t |
| -|- | 相邻 | numrange(1.1,2.2) -|- numrange(2.2,3.3) | t |
| + | 并 | numrange(5,15) + numrange(10,20) | [5,20) |
| * | 交 | int8range(5,15) * int8range(10,20) | [10,15) |
| - | 差 | int8range(5,15) - int8range(10,20) | [5,10) |
7.2 范围函数
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
| lower(anyrange) | 范围的元素类型 | 范围的下界 | lower(numrange(1.1,2.2)) | 1.1 |
| upper(anyrange) | 范围的元素类型 | 范围的上界 | upper(numrange(1.1,2.2)) | 2.2 |
| isempty(anyrange) | boolean | 范围为空？ | isempty(numrange(1.1,2.2)) | false |
| lower_inc(anyrange) | boolean | 下界包含在内？ | lower_inc(numrange(1.1,2.2)) | true |
| upper_inc(anyrange) | boolean | 上界包含在内？ | upper_inc(numrange(1.1,2.2)) | false |
| lower_inf(anyrange) | boolean | 下界无限？ | lower_inf(‘(,)’::daterange) | true |
| upper_inf(anyrange) | boolean | 上界无限？ | upper_inf(‘(,)’::daterange) | true |
| range_merge(anyrange, anyrange) | anyrange | 包含两个给定范围的最小范围 | range_merge(‘[1,2)’::int4range, ‘[3,4)’::int4range) | [1,4) |
8.聚集函数
8.1 常用函数
| 函数 | 参数类型 | 返回类型 | 部分模式 | 描述 |
|---|---|---|---|---|
| array_agg(expression) | 任何非数组类型 | 参数类型的数组 | No | 输入值（包括空）被连接到一个数组 |
| array_agg(expression) | 任意数组类型 | 和参数数据类型相同 | No | 输入数组被串接到一个更高维度的数组中 （输入必须都具有相同的维度并且不能为空或者 NULL） |
| avg(expression) | smallint, int, bigint、real、double precision、numeric或interval | 对于任何整数类型参数是numeric，对于一个浮点参数是double precision，否则和参数数据类型相同 | Yes | 所有非空输入值的平均值（算术平均） |
| bit_and(expression) | smallint、int、bigint或bit | 与参数数据类型相同 | Yes | 所有非空输入值的按位与，如果没有非空值则结果是空值 |
| bit_or(expression) | smallint, int, bigint, or bit | 与参数数据类型相同 | Yes | 所有非空输入值的按位或，如果没有非空值则结果是空值 |
| bool_and(expression) | bool | bool | Yes | 如果所有输入值为真则结果为真，否则为假 |
| bool_or(expression) | bool | bool | Yes | 至少一个输入值为真时结果为真，否则为假 |
| count(*) | bigint | Yes | 输入的行数 | |
| count(expression) | any | bigint | Yes | expression值非空的输入行的数目 |
| every(expression) | bool | bool | Yes | 等价于bool_and |
| json_agg(expression) | any | json | No | 将值，包含空值，聚集成一个 JSON 数组 |
| jsonb_agg(expression) | any | jsonb | No | 把值，包含空值，聚合成一个 JSON 数组 |
| json_object_agg(name, value) | (any, any) | json | No | 将名字/值对聚集成一个 JSON 对象，值可以为空，但不能是名字。 |
| jsonb_object_agg(name, value) | (any, any) | jsonb | No | 把名字/值对聚合成一个 JSON 对象，值可以为空，但不能是名字。 |
| max(expression) | 任意数组、数字、串、日期/时间、网络或者枚举类型，或者这些类型的数组 | 与参数数据类型相同 | Yes | 所有非空输入值中expression的最大值 |
| min(expression) | 任意数组、数字、串、日期/时间、网络或者枚举类型，或者这些类型的数组 | 与参数数据类型相同 | Yes | 所有非空输入值中expression的最小值 |
| string_agg(expression, delimiter) | (text, text) 或 (bytea, bytea) | 与参数数据类型相同 | No | 非空输入值连接成一个串，用定界符分隔 |
| sum(expression) | smallint、int、 bigint、real、double precision、numeric、 interval或money | 对smallint或int参数是bigint，对bigint参数是numeric，否则和参数数据类型相同 | Yes | 所有非空输入值的expression的和 |
| XMLagg(expression) | xml | xml | No | 连接非空XML值 |
8.2 统计类函数
| 函数 | 参数类型 | 返回类型 | 部分模式 | 描述 |
|---|---|---|---|---|
| array_agg(expression) | 任何非数组类型 | 参数类型的数组 | No | 输入值（包括空）被连接到一个数组 |
| array_agg(expression) | 任意数组类型 | 和参数数据类型相同 | No | 输入数组被串接到一个更高维度的数组中 （输入必须都具有相同的维度并且不能为空或者 NULL） |
| avg(expression) | smallint, int, bigint、real、double precision、numeric或interval | 对于任何整数类型参数是numeric，对于一个浮点参数是double precision，否则和参数数据类型相同 | Yes | 所有非空输入值的平均值（算术平均） |
| bit_and(expression) | smallint、int、bigint或bit | 与参数数据类型相同 | Yes | 所有非空输入值的按位与，如果没有非空值则结果是空值 |
| bit_or(expression) | smallint, int, bigint, or bit | 与参数数据类型相同 | Yes | 所有非空输入值的按位或，如果没有非空值则结果是空值 |
| bool_and(expression) | bool | bool | Yes | 如果所有输入值为真则结果为真，否则为假 |
| bool_or(expression) | bool | bool | Yes | 至少一个输入值为真时结果为真，否则为假 |
| count(*) | bigint | Yes | 输入的行数 | |
| count(expression) | any | bigint | Yes | expression值非空的输入行的数目 |
| every(expression) | bool | bool | Yes | 等价于bool_and |
| json_agg(expression) | any | json | No | 将值，包含空值，聚集成一个 JSON 数组 |
| jsonb_agg(expression) | any | jsonb | No | 把值，包含空值，聚合成一个 JSON 数组 |
| json_object_agg(name, value) | (any, any) | json | No | 将名字/值对聚集成一个 JSON 对象，值可以为空，但不能是名字。 |
| jsonb_object_agg(name, value) | (any, any) | jsonb | No | 把名字/值对聚合成一个 JSON 对象，值可以为空，但不能是名字。 |
| max(expression) | 任意数组、数字、串、日期/时间、网络或者枚举类型，或者这些类型的数组 | 与参数数据类型相同 | Yes | 所有非空输入值中expression的最大值 |
| min(expression) | 任意数组、数字、串、日期/时间、网络或者枚举类型，或者这些类型的数组 | 与参数数据类型相同 | Yes | 所有非空输入值中expression的最小值 |
| string_agg(expression, delimiter) | (text, text) 或 (bytea, bytea) | 与参数数据类型相同 | No | 非空输入值连接成一个串，用定界符分隔 |
| sum(expression) | smallint、int、 bigint、real、double precision、numeric、 interval或money | 对smallint或int参数是bigint，对bigint参数是numeric，否则和参数数据类型相同 | Yes | 所有非空输入值的expression的和 |
| xmlagg(expression) | xml | xml | No | 连接非空XML值 |
8.3 有序集聚集函数
| 函数 | 参数类型 | 返回类型 | 部分模式 | 描述 |
|---|---|---|---|---|
| corr(Y, X) | double precision | double precision | Yes | 相关系数 |
| covar_pop(Y, X) | double precision | double precision | Yes | 总体协方差 |
| covar_samp(Y, X) | double precision | double precision | Yes | 样本协方差 |
| regr_avgx(Y, X) | double precision | double precision | Yes | 自变量的平均值 （sum(X)/N） |
| regr_avgy(Y, X) | double precision | double precision | Yes | 因变量的平均值 （sum(Y)/N） |
| regr_count(Y, X) | double precision | bigint | Yes | 两个表达式都不为空的输入行的数目 |
| regr_intercept(Y, X) | double precision | double precision | Yes | 由（X, Y）对决定的最小二乘拟合的线性方程的 y截距 |
| regr_r2(Y, X) | double precision | double precision | Yes | 相关系数的平方 |
| regr_slope(Y, X) | double precision | double precision | Yes | 由（X, Y）对决定的最小二乘拟合的线性方程的斜率 |
| regr_sxx(Y, X) | double precision | double precision | Yes | sum(X^2) - sum(X)^2/N（自变量的“平方和”） |
| regr_sxy(Y, X) | double precision | double precision | Yes | sum(X*Y) - sum(X) * sum(Y)/N（自变量乘以因变量的“积之合”） |
| regr_syy(Y, X) | double precision | double precision | Yes | sum(Y^2) - sum(Y)^2/N（因变量的“平方和”） |
| stddev(expression) | smallint、int、 bigint、real、double precision或numeric | 浮点参数为double precision，否则为numeric | Yes | stddev_samp的历史别名 |
| stddev_pop(expression) | smallint、int、 bigint、real、double precision或numeric | 浮点参数为double precision，否则为numeric | Yes | 输入值的总体标准偏差 |
| stddev_samp(expression) | smallint、int、 bigint、real、double precision或numeric | 浮点参数为double precision，否则为numeric | Yes | 输入值的样本标准偏差 |
| variance(expression) | smallint、int、 bigint、real、double precision或numeric | 浮点参数为double precision，否则为numeric | Yes | var_samp的历史别名 |
| var_pop(expression) | smallint、int、 bigint、real、double precision或numeric | 浮点参数为double precision，否则为numeric | Yes | 输入值的总体方差（总体标准偏差的平方） |
| var_samp(expression) | smallint、int、 bigint、real、double precision或numeric | 浮点参数为double precision，否则为numeric | Yes | 输入值的样本方差（样本标准偏差的平方） |
8.4 有序数据集
| 函数 | 直接参数类型 | 聚集参数类型 | 返回类型 | 部分模式 | 描述 |
|---|---|---|---|---|---|
| mode() WITHIN GROUP (ORDER BY sort_expression) | 任何可排序类型 | 与排序表达式相同 | No | 返回最频繁的输入值（如果有多个频度相同的值就选第一个） | |
| percentile_cont(fraction) WITHIN GROUP (ORDER BY sort_expression) | double precision | double precision或者interval | 与排序表达式相同 | No | 连续百分率：返回一个对应于排序中指定分数的值，如有必要就在相邻的输入项之间插值 |
| percentile_cont(fractions) WITHIN GROUP (ORDER BY sort_expression) | double precision[] | double precision或者interval | 排序表达式的类型的数组 | No | 多重连续百分率：返回一个匹配fractions参数形状的结果数组， 其中每一个非空元素都用对应于那个百分率的值替换 |
| percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression) | double precision | 一种可排序类型 | 与排序表达式相同 | No | 离散百分率：返回第一个在排序中位置等于或者超过指定分数的输入值 |
| percentile_disc(fractions) WITHIN GROUP (ORDER BY sort_expression) | double precision[] | 任何可排序类型 | 排序表达式的类型的数组 | No | 多重离散百分率：返回一个匹配fractions参数形状的结果数组， 其中每一个非空元素都用对应于那个百分率的输入值替换 |
8.5 假想集聚集函数(排序)
| 函数 | 直接参数类型 | 聚集参数类型 | 返回类型 | 部分模式 | 描述 |
|---|---|---|---|---|---|
| rank(args) WITHIN GROUP (ORDER BY sorted_args) | VARIADIC “any” | VARIADIC “any” | bigint | No | 假想行的排名，为重复的行留下间隔 |
| dense_rank(args) WITHIN GROUP (ORDER BY sorted_args) | VARIADIC “any” | VARIADIC “any” | bigint | No | 假想行的排名，不留间隔 |
| percent_rank(args) WITHIN GROUP (ORDER BY sorted_args) | VARIADIC “any” | VARIADIC “any” | double precision | No | 假想行的相对排名，范围从 0 到 1 |
| cume_dist(args) WITHIN GROUP (ORDER BY sorted_args) VARIADIC “any” | VARIADIC “any” | double precision | No | 假想行的相对排名，范围从 1/N 到 1 |
8.6 分组操作
| 函数 | 返回类型 | 描述 |
|---|---|---|
| GROUPING(args…) | integer | 整数位掩码指示哪些参数不被包括在当前分组集合中 |
使用方法举例
WITH test_table AS (
SELECT UNNEST( ARRAY [ '财务', '行政', '销售', '财务', '行政', '行政' ] ) AS depart,
UNNEST ( ARRAY [ 'A', 'B', 'A', 'C', 'D', 'C' ] ) AS NAME,
UNNEST ( ARRAY [ 200, 100, 50, 30, 200, 100 ] ) AS donate
) SELECT
depart,
NAME,
GROUPING ( depart, NAME ),
SUM ( donate ),
COUNT ( donate )
FROM
test_table
GROUP BY
ROLLUP ( depart, NAME );
9.条件类函数
| 函数 | 语法 | 使用说明 | 使用例子 |
|---|---|---|---|
| case | CASE WHEN condition THEN result [WHEN …] [ELSE result] END | CASE子句可以用于任何表达式可以出现的地方。每一个condition是一个返回boolean结果的表达式。如果结果为真，那么CASE表达式的结果就是符合条件的result，并且剩下的CASE表达式不会被处理。如果条件的结果不为真，那么以相同方式搜寻任何随后的WHEN子句。如果没有WHEN condition为真，那么CASE表达式的值就是在ELSE子句里的result。如果省略了ELSE子句而且没有条件为真，结果为空。 | CASE WHEN a=1 THEN ‘one’ WHEN a=2 THEN ‘two’ ELSE 'other’END |
| coalesce | COALESCE(value [, …]) | 返回它的第一个非空参数的值。当且仅当所有参数都为空时才会返回空。它常用于在为显示目的检索数据时用缺省值替换空值。 | COALESCE(description, short_description, ‘(none)’) |
| nullif | NULLIF(value1, value2) | 当value1和value2相等时，NULLIF返回一个空值。 否则它返回value1。 | NULLIF(value, ‘(none)’) |
| greatest | GREATEST(value [, …]) | 从一个任意的数字表达式列表里选取最大的数值。列表中的 NULL 数值将被忽略。只有所有表达式的结果都是 NULL 的时候，结果才会是 NULL。 | greatest(2,5,1) |
| least | GREATEST(value [, …]) | 从一个任意的数字表达式列表里选取最小的数值。列表中的 NULL 数值将被忽略。只有所有表达式的结果都是 NULL 的时候，结果才会是 NULL。 | least(2,6,5) |
10.窗口函数
| 函数 | 返回类型 | 描述 |
|---|---|---|
| row_number() | bigint | 当前行在其分区中的行号，从1计 |
| rank() | bigint | 带间隙的当前行排名； 与该行的第一个同等行的row_number相同 |
| dense_rank() | bigint | 不带间隙的当前行排名； 这个函数计数同等组 |
| percent_rank() | double precision | 当前行的相对排名： (rank- 1) / (总行数 - 1) |
| cume_dist() | double precision | 累积分布：(在当前行之前或者平级的分区行数) / 分区行总数 |
| ntile(num_buckets integer) | integer | 从1到参数值的整数范围，尽可能等分分区 |
| lag(value anyelement [, offset integer [, default anyelement ]]) | 和value的类型相同 | 返回value，它在分区内当前行的之前offset个位置的行上计算；如果没有这样的行，返回default替代（必须和value类型相同）。offset和default都是根据当前行计算的结果。如果忽略它们，则offset默认是1，default默认是空值 |
| lead(value anyelement [, offset integer [, default anyelement ]]) | 和value类型相同 | 返回value，它在分区内当前行的之后offset个位置的行上计算；如果没有这样的行，返回default替代（必须和value类型相同）。offset和default都是根据当前行计算的结果。如果忽略它们，则offset默认是1，default默认是空值 |
| first_value(value any) | same type as value | 返回在窗口帧中第一行上计算的value |
| last_value(value any) | 和value类型相同 | 返回在窗口帧中最后一行上计算的value |
| nth_value(value any, nth integer) | 和value类型相同 | 返回在窗口帧中第nth行（行从1计数）上计算的value；没有这样的行则返回空值 |
11.查看内部所有函数
如何查看postgresql中所有的函数名称，SQL语句如下：
-- 查看所有函数名，返回类型，及参数个数
SELECT
pg_proc.proname AS "函数名称",
pg_type.typname AS "返回值数据类型",
pg_proc.pronargs AS "参数个数"
FROM
pg_proc
JOIN pg_type ON (pg_proc.prorettype = pg_type.oid)
-- WHERE pronamespace = (SELECT pg_namespace.oid FROM pg_namespace WHERE nspname = '模式')总结
到此这篇关于Postgresql常用函数及使用方法详解的文章就介绍到这了,更多相关Postgresql函数使用内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！
