PostgreSQL为内置的数据类型提供了大量的函数和运算符,用户也可以定义自己的函数和运算符,使用psql命令\df
和\do
可以列出所有可用的函数和运算符
1. 逻辑运算符
常用的逻辑运算符有AND、OR、NOT,逻辑系统有三个值true、fase和null,运算关系如下表所示:
a | b | a AND b | a OR b |
---|---|---|---|
TRUE | TRUE | TRUE | TRUE |
TRUE | FALSE | FALSE | TRUE |
TRUE | NULL | NULL | TRUE |
FALSE | FALSE | FALSE | FALSE |
FALSE | NULL | FALSE | NULL |
NULL | NULL | NULL | NULL |
a | NOT a |
---|---|
TRUE | FALSE |
FALSE | TRUE |
NULL | NULL |
2. 比较函数和运算符
2.1. 运算符
常用的比较运算符如下表所示:
运算符 | 描述 |
---|---|
< | 小于 |
> | 大于 |
<= | 小于等于 |
>= | 大于等于 |
= | 等于 |
<> | 不等于 |
!= | 不等于 |
2.2. 比较谓词
另外,还有一些用于比较运算的谓词,如下所示:
谓词 | 说明 | 示例 |
---|---|---|
BETWEEN AND | 包括范围端点 | 2 BETWEEN 1 AND 3 --> t 2 BETWEEN 3 AND 1 --> f |
NOT BETWEEN AND | 2 NOT BETWEEN 1 AND 3 --> f | |
BETWEEN SYMMETRIC AND | 两个端点值排序后比较 | 2 BETWEEN SYMMETRIC 3 AND 1 --> t |
NOT BETWEEN SYMMETRIC AND | 2 NOT BETWEEN SYMMETRIC 3 AND 1 --> f | |
IS DISTINCT FROM | 不相等,将null视为可比值 | 1 IS DISTINCT FROM NULL --> t NULL IS DISTINCT FROM NULL --> f |
IS NOT DISTINCT FROM | 相等,将null视为可比值 | 1 IS NOT DISTINCT FROM NULL --> t NULL IS NOT DISTINCT FROM NULL --> f |
IS NULL | 1.5 IS NULL --> f | |
IS NOT NULL | ‘null’ IS NOT NULL --> t | |
ISNULL | 非标准语法 | |
NOTNULL | ||
IS TRUE | true IS TRUE --> t NULL::boolean IS TRUE --> f | |
IS NOT TRUE | true IS NOT TRUE --> f NULL::boolean IS NOT TRUE --> t | |
IS FALSE | true IS FALSE --> f NULL::boolean IS FALSE --> f | |
IS NOT FALSE | true IS NOT FALSE --> f NULL::boolean IS NOT FALSE --> t | |
IS UNKNOWN | true IS UNKNOWN --> f NULL::boolean IS UNKNOWN --> t | |
IS NOT UNKNOWN | 布尔值是true或false | true IS NOT UNKNOWN --> t NULL::boolean IS NOT UNKNOWN --> f |
2.3. 比较函数
常用的比较函数如小表所示:
函数 | 说明 | 示例 |
---|---|---|
num_nonnulls(VARIADIC “any”) | 返回非null的个数 | num_nonnulls(1, NULL, 2) ->2 |
num_nulls(VARIADIC “any”) | 返回null的个数 | num_nulls(1, NULL, 2) -> 1 |
3. 数学函数和运算符
3.1. 数学运算符
常用的数学运算符如下表所示:
符号 | 说明 | 示例 |
---|---|---|
+ | 加号 | 2 + 3 -> 5 |
+ | 正 | + 3.5 -> 3.5 |
- | 减号 | 2 - 3 -> -1 |
- | 负 | -(-4) -> 4 |
* | 乘 | 2 * 3 -> 6 |
/ | 除 | 5.0 / 2 -> 2.5 |
% | 取模 | 5 % 4 -> 1 |
^ | 立方 | 2^3 -> 8 |
|/ | 平方根 | |/ 25.0 -> 5 |
||/ | 立方根 | ||/ 64.0 -> 4 |
@ | 绝对值 | @ -5.0 -> 5.0 |
& | 与 | 91 & 15 -> 11 |
| | 或 | 32 | 3 -> 35 |
# | 异或 | 17 # 5 -> 20 |
~ | 取反 | ~1 -> -2 |
<< | 按位向左移 | 1 << 4 -> 16 |
>> | 按位向右移 | 8 >> 2 -> 2 |
3.2. 数学函数
常用数学函数如下表所示:
函数名 | 说明 | 示例 |
---|---|---|
abs(n) | 取绝对值 | abs(-17.4) -> 17.4 |
cbrt(d) | 取立方根 | cbrt(64.0) -> 4 |
ceil(n)/ceil(d) | 向上取整 | ceil(42.2) -> 43 ceil(-42.8) -> -42 |
ceiling(n)/ceiling(d) | 向上取整 | ceiling(95.3) -> 96 |
degrees(d) | 将弧度转为度 | degrees(0.5) -> 28.64788975654116 |
div(y, x) | y/x的整数商 | div(9, 4) -> 2 |
erf(d) | erf(1.0) -> 0.8427007929497149 | |
erfc(d) | erfc(1.0) -> 0.15729920705028513 | |
exp(n)/exp(d) | 指数 | exp(1.0) -> 2.7182818284590452 |
factorial(n) | 阶乘 | factorial(5) -> 120 |
floor(n)/floor(d) | 向下取整 | floor(42.8) -> 42 floor(-42.8) -> -43 |
gcd(n, n) | 最大公约数 | gcd(1071, 462) -> 21 |
lcm(n, n) | 最小公倍数 | lcm(1071, 462) -> 23562 |
ln(n)/ln(d) | 自然对数 | ln(2.0) -> 0.6931471805599453 |
log(n)/log(d) | 10为底的对数 | log(100) -> 2 |
log10(n)/log10(d) | log10(1000) -> 3 | |
log(b,x) | 以b为底的x对数 | log(2.0, 64.0) -> 6.000 |
min_scale(n) | 最小刻度 | min_scale(8.4100) -> 2 |
mod(y, x) | y/x的余数 | mod(9, 4) -> 1 |
pi() | π | pi() -> 3.141592653589793 |
power(a, b) | a的b次方 | power(9, 3) -> 729 |
radians(d) | 将度数转为弧度 | radians(45.0) -> 0.7853981633974483 |
round(n)/round(d) | 四舍五入 | round(42.4) -> 42 |
round(v, s) | 四舍五入,保留s位小数 | round(42.4382, 2) -> 42.44 |
scale(n)/scale(d) | 获取小数位数 | scalse(8.4100) -> 4 |
sign(n)/sign(d) | 获取符号(-1, 0, +1) | sign(-8.4) -> -1 |
sqrt(n)/sqrt(d) | 平方根 | sqrt(2) -> 1.4142135623730951 |
trim_scale(n) | 删除小数后的0 | trim_scale(8.4100) -> 8.41 |
trunc(n)/trunc(d) | 截断为整数 | trunc(42.8) -> 42 |
trunc(v, s) | 截断v到小数点后的s位 | trunc(42.4382, 2) |
width_bucket | width_bucket(5.35, 0.024, 10.06, 5) -> 3 | |
width_bucket | width_bucket(now(), array[‘yesterday’, ‘today’, ‘tomorrow’]::timestamptz[]) -> 2 |
3.3. 随机函数
常用随机函数如下表所示:
函数名 | 说明 | 示例 |
---|---|---|
random() | 获取0.0到1.0之间的随机值 | random() -> 0.897124072839091 |
random_normal(mean, stddev) | 从给定数的正态分布中返回随机数,mean默认0.0,stddev默认1.0 | random_normal(0.0, 1.0) -> 0.051285419 |
setseed(d) | 给random()和random_normal()设置调用种子,-1.0~1.0 | setseed(0.12345) |
3.4. 三角函数
常用三角函数如下表所示:
函数名 | 说明 | 示例 |
---|---|---|
acos(d) | 反余弦,返回弧度 | acos(1) -> 0 |
acosd(d) | 反余弦,返回度 | acosd(0.5) -> 60 |
asin(d) | 反正弦,返回弧度 | asin(1) -> 1.5707963267948966 |
asind(d) | 反正弦,返回度 | asind(0.5) -> 30 |
atan(d) | 反正切,返回弧度 | atan(1) -> 0.7853981633974483 |
atand(d) | 反正切,返回度 | atand(1) -> 45 |
atan2(y, x) | y/x的反正切,返回弧度 | atan2(1, 0) -> 1.5707963267948966 |
atan2d(y, x) | y/x的反正切,返回度 | atan2d(1, 0) -> 90 |
cos(d) | 余弦,返回弧度 | cos(0) -> 1 |
cosd(d) | 余弦,返回度 | cosd(60) -> 0.5 |
cot(d) | 余切,返回弧度 | cot(0.5) -> 1.830487721712452 |
cotd(d) | 余切,返回度 | cotd(45) -> 1 |
sin(d) | 正弦,返回弧度 | sin(1) -> |
sind(d) | 正弦,返回度 | sind(30) -> 0.5 |
tan(d) | 正切,返回弧度 | tan(1) -> |
tand(d) | 正切,返回度 | tand(45) -> 1 |
3.5. 双曲线函数
常用双曲线函数如下表所示:
函数名 | 说明 | 示例 |
---|---|---|
sinh(d) | 双曲正弦 | sinh(1) -> 1.1752011936438014 |
cosh(d) | 双曲余弦 | cosh(0) -> 1 |
tanh(d) | 双曲正切 | tanh(1) -> 0.7615941559557649 |
asinh(d) | 反双曲正弦 | asinh(1) -> 0.881373587019543 |
acosh(d) | 反双曲余弦 | acosh(1) -> 0 |
atanh(d) | 反双曲正切 | atanh(0.5) -> .5493061443340548 |
4. 字符串函数和运算符
常用字符串函数如下表所示:
名称 | 说明 | 示例 |
---|---|---|
|| | 拼接 | |
btrim(s, c) | 移除指定字符c | btrim(‘xyxtrimyyx’, ‘xy’) -> trim |
IS [NOT] [form] NORMALIZED | 检查字符串是否为指定的Unicode形式,默认NFC,还有NFD、NFKC、NFKD | U& ‘\0061\0308bc’ IS NFD NORMALIZED -> t |
bit_length(text) | 返回字符串位数 | bit_length(‘jose’) -> 32 |
char_length(text) | 返回字符串字符数 | char_length(‘jose’) -> 4 |
lower(text) | 转小写 | lower(‘TOM’) -> tom |
lpad(s, length, [fill text]) | 填充指定字符 | lpad(‘hi’, 5, ‘xy’) -> xyxhi |
ltrim(str, [char]) | 移除指定字符 | ltrim(‘zzzytest’, ‘xyz’) -> test |
normalize(t, [form]) | normalize(U& ‘\0061\0308bc’, NFC) -> U& ‘\00E4bc’ | |
octet_length(t) | octet_length(‘jose’) -> 5 | |
octet_length(char) | octet_length('abc '::character(4)) -> 4 | |
overlay(str PLACING newStr FROM start [FOR count]) | 从头start开始替换count个字符 | overlay(‘Txxxxas’ placing ‘hom’ from 2 from 4) -> Thomas |
position(substr IN str) | position(’ om’ in ‘Thomas’ -> 3) | |
rpad(str, len, [fill]) | 将fill拼接到str后面 | rpad(‘hi’, 5, ‘xy’) -> hixyx |
rtrim(str, [char]) | 移除含有char的字符 | rtrim(‘testxxzx’, ‘xyz’) -> test |
substring(str [FROM start] [FOR count]) | 从start开始截取count位字符 | substring(‘Thomas’ from 2 for 3) -> hom |
substring(str FROM pattern) | substring(‘Thomas’ from ‘…$’) -> mas | |
substring(str SIMILAR pattern ESCAPE escape) substring(str FROM pattern FOR escape) | substring(‘Thomas’ similar ‘%#“o_a#”_’ escape ‘#’) -> oma | |
trim([LEADING | TRAILING | BOTH] [char] FROM str) |
trim([LEADING | TRAILING | BOTH] [FROM] str, [char]) |
upper(text) | 转大写 | upper(‘tom’) -> TOM |
text ^@ text | 第一个字符以第二个字符开头时返回true | ‘alphabet’ ^@ ‘alph’ -> true |
concat(val1 “any” [, val2 “any” [, …]]) | 连接不为null的字符 | concat(‘abcde’, 2, NULL, 22) -> abcde222 |
concat_ws(sep text, val1 “any” [, val2 “any” [, …]]) | 用指定分隔符连接不为null的字符 | concat_ws(‘,’, ‘abcde’, 2, NULL, 22) -> abcde, 2, 22 |
format(formatstr text [, formatarg “any” [, …]]) | 格式化字符串 | format(‘Hello %s, %1$s’, ‘World’) -> Hello World, World |
initcap(text) | 将单词第一个字母大写,其他小写 | initcap(‘hi THOMAS’) -> Hi Thomas |
left(string text, n integer) | 返回前n个字符 | left(‘abcde’, 2) -> ab |
length(text) | 返回字符长度 | length(‘jose’) -> 4 |
md5(text) | 返回md5加密后的值 | md5(‘abc’) -> 900150983cd24fb0d6963f7d28e17f72 |
pg_client_encoding() | 返回当前编码 | pg_client_encoding() -> UTF8 |
quote_literal(anyelement) | 将给定的值转换为文本 | quote_literal(42.5) -> ‘42.5’ |
quote_nullable(anyelement) | 将给定的值转换为文本,如果为Null,返回null | quote_nullable(42.5) -> ‘42.5’ |
repeat(string text, number integer) | 重复n次 | repeat(‘Pg’, 4) -> PgPgPg |
replace(string text, from text, to text) | 将from替换为to | replace(‘abcdefabcdef’, ‘cd’, XX) -> abXXefabXXef |
reverse(text) | 反转 | reverse(‘abcde’) -> edcba |
right(string text, n integer) | 返回后n个字符 | right(‘abcde’, 2) -> de |
split_part(string text, delimiter text, n integer) | split_part(‘abc~@~def~@~ghi’, ‘~@~’, 2) -> def | |
starts_with(string text, prefix text) | 判断是否已prefix开头 | starts_with(‘alphabet’, ‘alph’) -> true |
substr(string text, start integer [, count integer]) | 从start开始截取count位字符 | substr(‘alphabet’, 3) -> phabet substr(‘alphabet’, 3, 2) -> ph |
to_hex(integer) | 将数字转换为十六进制数 | to_hex(2147483647) -> 7fffffff |
5. 格式化函数
常用的格式化函数如下表所示:
函数 | 描述 | 示例 |
---|---|---|
to_char( timestamp, text) | 根据给定的格式将时间戳转换为字符串 | to_char(timestamp ‘2024-05-05 17:31:12.66’, ‘HH12:MI:SS’) -> 05:31:12 |
to_char(interval, text) | 根据给定的格式将间隔转换为字符串 | to_char(interval ‘15h 2m 12s’, ‘HH24:MI:SS’) -> 15:02:12 |
to_char(numeric, text) | 根据给定的格式将数字转换为字符串 | to_char(125, ‘999’) -> 125 to_char(125.8::real, ‘999D9’) -> 125.8 to_char(-125.8, ‘999D99S’) -> 125.80- |
to_date(text, text) | 根据给定的格式将字符串转换为日期 | to_date(‘05 Dec 2023’, ‘DD Mon YYYY’) -> 2023-12-05 |
to_number(text, text) | 根据给定的格式将字符串转换为数字 | to_number(‘12454.8-’, ‘99G999D9S’) -> -12454.8 |
to_timestamp(text, text) | 根据给定的格式将字符串转换为时间戳 | to_timestamp(‘05 Dec 2023’, ‘DD Mod YYYY’) -> 2023-12-05 00:00:00-05 |
用于数字格式的模板样式如下表所示:
符号 | 描述 |
---|---|
9 | |
0 | |
. | |
, | |
PR | 括号中的负值 |
S | 负号 |
L | 货币符号 |
D | 小数点 |
G | 组分隔符 |
MI | 小于0时减号 |
PL | 大于0时加号 |
SG | 加号/减号 |
RN | 1~3999之间随机数 |
TH or th | 序列号后缀 |
V | |
EEEE |
6. 日期时间函数和运算符
6.1. 日期时间运算符
常用的日期时间运算符如下表所示:
运算符 | 描述 | 示例 |
---|---|---|
date + integer | 日期加天数 | date ‘2024-05-05’ + 7 -> 2024-05-12 |
date + interval | 日期加时间间隔 | date ‘2024-05-05’ + interval ‘1 hour’ -> 2024-05-05 01:00:00 |
date + time | 日期加时间 | date ‘2024-05-05’ + time ‘03:00’ -> 2024-05-05 03:00:00 |
interval + interval | 时间间隔相加 | interval ‘1 day’ + interval ‘1 hour’ -> 1 day 01:00:00 |
timestamp + interval | 时间戳加时间间隔 | timestamp ‘2024-05-05 01:00’ + interval ‘23 hours’ -> 2024-05-06 00:00:00 |
time + interval | 时间加时间间隔 | time ‘01:00’ + interval + ‘3 hours’ -> 04:00:00 |
- interval | - interval ‘23 hours’ -> -23:00:00 | |
date - date | 日期相隔天数 | date ‘2024-05-05’ - date ‘2024-05-02’ -> 3 |
date - integer | n天前 | date ‘2024-05-05’ - 3 -> 2024-05-02 |
date - interval | 日期减时间间隔 | date ‘2024-05-05’ - interval ‘1 hour’ -> 2024-05-04 23:00:00 |
time - time | 相隔多长时间 | time ‘05:00’ - time ‘03:00’ -> 02:00:00 |
time - interval | 时间减时间间隔 | time ‘05:00’ - interval ‘2 hours’ -> 03:00:00 |
timestamp - interval | 时间戳减时间间隔 | timestamp ‘2024-05-05 23:00’ - interval ‘23 hours’ -> 2024-05-05 00:00:00 |
interval - interval | 时间间隔相减 | interval ‘1 day’ - interval ‘1 hour’ -> 1day -01:00:00 |
interval * double precision | 时间间隔乘以小数 | interval ‘1 second’ * 900 -> 00:15:00 interval ‘1 day’ * 21 -> 21 days interval ‘1 hour’ * 3.5 -> 03:30:00 |
interval / double precision | 时间间隔除以小数 | interval ‘1 hour’ / 1.5 -> 00:40:00 |
6.2. 日期时间函数
常用的日期时间函数如下表所示:
函数 | 描述 | 示例 |
---|---|---|
age(timestamp, timestamp) | age(timestamp ‘2024-05-05’, timestamp ‘2010-03-15’) -> 14 years 1 mon 21 days | |
age(timestamp) | 当前日期相减 | age(timestamp ‘2010-03-15’) -> 14 years 1 mon 24 days |
clock_timestamp() | 当前日期时间带时区 | clock_timestamp() -> 2024-05-08 11:45:59.727 +0800 |
current_date | 当前日期 | current_date -> 2024-05-05 |
current_time | 当前时间 | current_time -> 14:39:53.662522+08 |
current_time(integer) | 当前时间带时区 | 14:39:53.66+08 |
current_timestamp | 当前日期时间带时区 | current_timestamp(0) -> 2024-05-05 14:39:53+08 |
date_add(timestamp with time zone, interval [, text]) | 日期相加 | date_add(‘2024-05-05 00:00:00+08’ :: timestamptz, ‘1 day’ :: interval, ‘Asia/Shanghai’) -> 2024-05-06 00:00:00+08 |
date_bin(interval, timestamp, timestamp) | date_bin(‘15 minutes’, timestamp ‘2024-05-05 09:40:00’, timestamp ‘2024-05-05 09:05:00’) -> 2024-05-05 09:35:00.000 | |
date_part(text, timestamp) | 获取小时数 | date_part(‘hour’, timestamp ‘2024-05-05 09:40:00’) -> 09 |
date_part(text, interval) | date_part(‘month’, interval ‘2 years 3 months’) -> 3 | |
date_subtract(timestamp with time zone, intervl [, text]) | 日期相减 | date_subtract(‘2024-05-05 00:00:00+08’::timestamptz, ‘1 day’::interval, ‘Asia/Shanghai’) -> |
date_trunc(text, timestamp) | 只获取日期和小时 | date_trunc(‘hour’, timestamp ‘2024-05-05 09:40:00’) -> 2024-05-05 09:00:00 |
date_trunc(text, timestamp with time zone, text) | 获取指定时区的日期 | date_trunc(‘day’, timestamptz ‘2024-05-05 09:40:00’, ‘Asia/Shanghai’) -> |
date_trunc(text, interval) | date_trunc(‘hour’, interval ‘2 days 3 hours 40 minutes’) -> 2 days 03:00:00 | |
localtime | 获取当前时间 | localtime -> 09:40:53.662522 |
localtime(integer) | 获取当前时间 | localtime(0) -> 09:40:53 |
localtimestamp | 获取当前日期时间 | localtimestamp -> 2024-05-05 09:40:00.662522 |
localtimestamp(integer) | 获取当前日期时间 | localtimestamp(2) -> 2024-05-05 09:40:00.66 |
make_date(year int, month int, day int) | make_date(2024, 05, 05) -> 2024-05-05 | |
make_interval([years int [, months int [, weeks int [, days int [, hours int [, mins int [, secs double precision ]]]]]]]) | make_interval(days => 10) -> 10 days | |
make_time(hour int, min int, sec double precision) | 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) | make_timestamp(2024, 05, 05, 8, 15, 23.5) -> 2024-05-05 08:15:23.5 | |
make_timestamptz(year int, month int, day int, hour int, min int, sec double precision [, timezone text]) | make_timestamptz(2024, 5, 5, 8, 15, 23.5) -> 2024-05-05 08:15:23.5+08 make_timestamptz(2024, 5, 5, 8, 15, 23.5, ‘’) -> 2024-05-05 08:15:23.5+08 | |
now() | 获取当前日期时间 | now() -> 2024-05-05 09:40:00.662522+08 |
statement_timestamp() | 当前日期时间 | statement_timestamp() -> 2024-05-05 14:04:32.735 +0800 |
timeofday() | 当前日期时间 | timeofday() -> |
transaction_timestamp() | 当前日期时间 | transaction_timestamp() -> |
to_timestamp(double precision) | 转换时间戳 | to_timestamp(23343) -> |
7. 开窗函数
常用的开窗函数如下表所示:
函数 | 描述 |
---|---|
row_number() | 返回当前行在其分区内的编号,从1开始 |
rank() | 返回当前行的列组 |
dense_rank() | 返回当前行的排名 |
percent_rank | 返回当前行的相对排名,即(排名-1)/(总分区行数-1) |
cume_dist() | 返回累积分布,即(当前行前面或与当前行对等的分区行数)/(分区行总数) |
ntile(num integer) | 返回一个从1到参数值的整数 |
lag(value anycompatible [, offset integer [, default anycompatible]]) | |
lead(value anycompatible [, offset integer [, default anycompatible]]) | |
first_value(value anyelement) | 返回在窗口框架的第一行计算的值 |
last_value(value anyelement) | 返回在窗口框架的最后行计算的值 |
nth_value(value anyelement, n integer) | 返回在窗口框架的第n行计算的值 |