PostgreSQL函数和运算符

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行计算的值