PostgreSQL的常用函数（持续更新）

最新推荐文章于 2024-07-06 01:00:05 发布

平海和风

最新推荐文章于 2024-07-06 01:00:05 发布

阅读量262

点赞数 10

文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/weixin_42532327/article/details/138165175

版权

简单函数

ABS(x)：返回x的绝对值
MOD(x,y)：返回x被y除后的余数，对于带有小数部分的数值也起作用，返回除法运算后的精确余数
CEIL(x)和CEILING(x)：返回不小于x的最小整数值，返回值转化为一个BIGINT
FLOOR(x)：返回不大于x的最大整数值
ROUND(x)：回值更接近于参数x的整数，对x值进行四舍五入
ROUND(x,y)：y作用是指定x的小数点对应位置进行四舍五入，如round(1.386,2)结果是1.39
MAX(T)：某列最大值
MIN()：某列最小值
AVG()：某列平均值
SUM()：某列求和
random()：返回0-1之间的随机数
trunc(100.235, 2)：返回截断后的值
SIGN(x)：判断x的值并返回负-1、零0或正1
char_length(str) / length(str)：返回字符串str所包含的字符个数，区别在于前者计算汉字是一个字符，后者汉字是三个字符（uft8）或者两个字符
CONCAT(s1,s2,...)：返回连接参数产生的字符串
CONCAT_WS(x,s1,s2,...)：x作为分隔符放在要连接的两个字符串之间
LEFT(s,n)：返回字符串s最左边的n个字符
RIGTHT(s,n)：返回字符串s最右边的n个字符
LTRIM(str)：返回结果删除左侧的空格
RTRIM(str)：返回结果删除右侧的空格
TRIM(str)：删除字符串s两侧的空格
TRIM(s1 FROM s)：删除s两端有s1的字符串，如果未指定，就删除空格
REPEAT(s,n)：复制n次字符串s
REPLACE(s,s1,s2)：把s里的s1替换成s2
SUBSTRING(s,n.len)：返回s从n开始往后len长度的字符串
REVERSE(s)：字符串反转
POSITION(str1 IN str)：返回子字符串str1在字符串str中的开始位置
cast(a as t)：类型转换把a转换成t，如把字符串转换成数值
MD5(str)：返回md5加密结果
ENCODE(str, pwd)：加密，pwd可以是base64、hex
DECODE(str, pwd)：解密
string_agg(text, text)：将多行结果字符串拼接到一行，用于分组查询
to_date(str, 'yyyy-MM')：按格式返回date类型
to_char(x, text)：按格式返回字符串类型
row_to_json(table_name)：数据转换为json格式
lower()：字符串转为小写
upper()：字符串转为大写
substring(str, a, b)：截取str从a开始的总共b长度的串
COALESCE(v1, v2, ...)：返回第一个非null的值
replace(str, v1, v2)：将str中的v1替换成v2
translate(string text, from text, to text)：test匹配出from，替换成to，规则为from和to一一对应，from多余出的删除
position(s in str)：返回子字符串在字符串的位置
strpos(str, s)：功能和position相同
convert(string bytea,src_encoding name, dest_encodingname)：将字符串从指定编码转换至目的编码格式

关键字

RETURNING

返回DML修改的数据

INSERT语句后接RETURNING属性返回插入的数据；
INSERT INTO table_name(字段) VALUES (字段值) RETURNING *;

UPDATE语句后接RETURNING属性返回更新后的新值；
UPDATE table_name SET 字段='p' WHERE ... RETURNING *;

DELETE语句后接RETURNING属性返回删除的数据。
DELETE FROM table_name WHERE ... RETURNING *;

UPSERT

用来解决在数据插入过程中数据冲突的情况下，不影响整个事务的回滚

INSERT…ON CONFLICT UPDATE

插入的数据冲突时不报错，同时更新冲突的数据
INSERT INTO table_name(k1, k2) VALUES (v1, v2) ON CONFLICT(冲突键) DO UPDATE SET k3 = v3, update_time = now(); 

插入的数据冲突时什么都不做
INSERT INTO table_name(k1, k2) VALUES (v1, v2) ON CONFLICT(冲突键) DO NOTHING;

delete、truncate、drop

三者区别主要体现在删除对象、删除条件支持、执行速度、回滚支持、自增初始化、空间释放等方面。

删除对象：delete和truncate只删除表数据，不删除表结构；drop删除表的结构、数据、索引、约束和触发器。
删除条件支持：delete支持where条件，用于删除部分或全部数据；truncate和drop不支持where条件，执行整体删除。
执行速度：delete的执行速度相对较慢，因为它会记录每个删除操作的日志，适合于小量或部分数据的删除；truncate和drop的执行速度较快，因为它们不记录单个删除操作的日志，适合于大量或全部数据的快速删除。
回滚支持：delete属于DML（数据操纵语言），支持事务回滚操作，可以在事务未提交前撤销删除；truncate和drop属于DDL（数据定义语言），执行后立即生效，不能回滚。
自增初始化：delete不会重置自增字段的初始值；truncate会重置自增列为1。
空间释放：delete不会减少表或索引所占用的空间；truncate后表和索引所占用的空间会恢复到初始大小；drop会释放表所占用的所有空间。
应用范围：delete可以是table和view；truncate只能对table。

复杂函数SQL

条件判断

如果expr值等于某个vn值，则返回对应位置的THEN后面的结果；如果与所有值都不相等，则返回ELSE后面的rn
CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END

某个vn值为TRUE时，返回对应的位置THEN后面的结果；如果所有值都不为TRUE，则返回ELSE后面的rn
CASE WHEN v1 THEN r1 [WHEN v2 THEN v2] ELSE rn END

分组排序

ROW_NUMBER () OVER ( PARTITION BY 分组列 ORDER BY 排序列 DESC ) AS rownum
over里的分组及排序晚于where、group by、order by的执行
由于执行顺序，使用时可以作为子查询临时表，再在主查询where rownum = 1 获取

WITH AS（递归查询、子查询）

递归函数，结果包含本级及下级所有数据
with RECURSIVE [tableName] x as (
	select * from t1 where id = '0'
	union all
	select b.* from t2 b where b.parent_id = x.id
) select * from x

临时表单表
WITH tmp AS (
    SELECT * from t where ...
)
SELECT * from tmp 

临时表作为条件
with tmp as (
	select * from t where ...
) select * from b where key1 in (select key2 from tmp)

多表结果合集
WITH table1 AS (
    SELECT name FROM t1 WHERE ...
),
table2 AS (
    SELECT name FROM t2 WHERE ...
)
SELECT * FROM table1
UNION ALL
SELECT * FROM table2;

批量插入

INSERT INTO table_name SELECT...FROM source_table 

INSERT INTO table_name(字段1, 字段2...) VALUES ()

系统函数

查询版本号
select version();
查询当前用户
select USER, CURRENT_USER;