postgresql 内置函数有那些
PostgreSQL 提供了非常丰富和强大的内置函数库，涵盖多个类别。以下是主要的内置函数分类及常用示例：
1. 数学函数
表格
复制
| 函数 | 说明 | 示例 |
|---|---|---|
ABS(x) | 绝对值 | ABS(-10)→ 10 |
ROUND(x, d) | 四舍五入 | ROUND(3.14159, 2)→ 3.14 |
CEIL(x)/FLOOR(x) | 向上/向下取整 | CEIL(3.14)→ 4 |
POWER(a, b) | 幂运算 | POWER(2, 3)→ 8 |
SQRT(x) | 平方根 | SQRT(16)→ 4 |
CBRT(x) | 立方根 | CBRT(27)→ 3 |
LN(x)/LOG(x) | 自然对数/常用对数 | LN(2.0)→ 0.693... |
RANDOM() | 0-1之间的随机数 | RANDOM() |
MOD(y, x) | 取模 | MOD(10, 3)→ 1 |
2. 字符串函数
表格
复制
| 函数 | 说明 | 示例 |
|---|---|---|
CONCAT(str1, str2, ...) | 字符串连接 | CONCAT('Hello', ' ', 'World') |
LENGTH(str) | 字符串长度 | LENGTH('PostgreSQL')→ 10 |
UPPER(str)/LOWER(str) | 大小写转换 | UPPER('hello')→ 'HELLO' |
SUBSTRING(str FROM start [FOR count]) | 子字符串 | SUBSTRING('PostgreSQL' FROM 1 FOR 5)→ 'Postg' |
REPLACE(str, from, to) | 替换 | REPLACE('abc', 'b', 'X')→ 'aXc' |
TRIM([BOTH/LEADING/TRAILING] [chars] FROM str) | 去除空格/字符 | TRIM(' hello ')→ 'hello' |
LEFT(str, n)/RIGHT(str, n) | 左/右截取 | LEFT('PostgreSQL', 4)→ 'Post' |
POSITION(substr IN str) | 查找位置 | POSITION('gre' IN 'PostgreSQL')→ 5 |
STRING_AGG(expr, delimiter) | 字符串聚合 | STRING_AGG(name, ',') |
3. 日期时间函数
表格
复制
| 函数 | 说明 | 示例 |
|---|---|---|
NOW()/CURRENT_TIMESTAMP | 当前日期时间 | 返回带时区的时间戳 |
CURRENT_DATE/CURRENT_TIME | 当前日期/时间 | |
EXTRACT(field FROM timestamp) | 提取日期部分 | EXTRACT(YEAR FROM NOW()) |
DATE_TRUNC(field, timestamp) | 截断到指定精度 | DATE_TRUNC('month', NOW()) |
AGE(timestamp, timestamp) | 计算年龄/间隔 | AGE('2030-04-10', '2000-06-13') |
TO_CHAR(timestamp, format) | 格式化日期为字符串 | TO_CHAR(NOW(), 'YYYY-MM-DD') |
TO_DATE(text, format) | 字符串转日期 | TO_DATE('2023-12-25', 'YYYY-MM-DD') |
TO_TIMESTAMP(text, format) | 字符串转时间戳 | |
INTERVAL '1 day' | 时间间隔 | NOW() + INTERVAL '1 month' |
4. 聚合函数
表格
复制
| 函数 | 说明 |
|---|---|
COUNT(*)/COUNT(expr) | 计数 |
SUM(expr) | 求和 |
AVG(expr) | 平均值 |
MAX(expr)/MIN(expr) | 最大/最小值 |
ARRAY_AGG(expr) | 聚合为数组 |
JSON_AGG(expr) | 聚合为JSON |
STRING_AGG(expr, delimiter) | 字符串聚合（列转行） |
5. 窗口函数
表格
复制
| 函数 | 说明 |
|---|---|
ROW_NUMBER() OVER (...) | 行号 |
RANK() OVER (...) | 排名（跳跃） |
DENSE_RANK() OVER (...) | 密集排名 |
LEAD(expr, offset)/LAG(expr, offset) | 前/后偏移值 |
FIRST_VALUE(expr)/LAST_VALUE(expr) | 首/尾值 |
SUM(expr) OVER (...) | 累计求和 |
AVG(expr) OVER (...) | 移动平均 |
6. 条件函数
表格
复制
| 函数 | 说明 | 示例 |
|---|---|---|
COALESCE(val1, val2, ...) | 返回第一个非NULL值 | COALESCE(NULL, NULL, 'default')→ 'default' |
NULLIF(val1, val2) | 相等返回NULL | NULLIF(10, 10)→ NULL |
GREATEST(val1, val2, ...) | 最大值 | GREATEST(1, 5, 3)→ 5 |
LEAST(val1, val2, ...) | 最小值 | LEAST(1, 5, 3)→ 1 |
CASE WHEN ... THEN ... END | 条件判断 | 标准SQL条件表达式 |
7. 类型转换函数
表格
复制
| 函数 | 说明 |
|---|---|
CAST(expr AS type) | 标准类型转换 |
expr::type | PostgreSQL特有的类型转换语法 |
TO_CHAR(number/date, format) | 转为字符串 |
TO_NUMBER(text, format) | 字符串转数字 |
TO_DATE(text, format) | 字符串转日期 |
TO_TIMESTAMP(text, format) | 字符串转时间戳 |
8. JSON 函数
表格
复制
| 函数 | 说明 |
|---|---|
JSON_BUILD_OBJECT(key, val, ...) | 构建JSON对象 |
JSON_BUILD_ARRAY(val1, val2, ...) | 构建JSON数组 |
JSON_OBJECT_AGG(key, value) | 聚合为JSON对象 |
JSON_ARRAY_AGG(expr) | 聚合为JSON数组 |
->/->> | 获取JSON字段（返回json/text） |
#>/#>> | 按路径获取JSON字段 |
JSONB_SET(target, path, new_value) | 更新JSONB值 |
9. 数组函数
表格
复制
| 函数 | 说明 |
|---|---|
ARRAY[elem1, elem2, ...] | 创建数组 |
ARRAY_LENGTH(array, dim) | 数组长度 |
ARRAY_APPEND(array, elem) | 添加元素 |
ARRAY_CAT(array1, array2) | 连接数组 |
UNNEST(array) | 数组展开为多行 |
ANY(array)/ALL(array) | 数组比较 |
@>/<@ | 包含操作符 |
10. 系统/元数据函数
表格
复制
| 函数 | 说明 |
|---|---|
VERSION() | 数据库版本 |
CURRENT_USER/SESSION_USER | 当前用户 |
CURRENT_DATABASE() | 当前数据库名 |
PG_SIZE_PRETTY(bytes) | 格式化字节大小 |
PG_RELATION_SIZE(relname) | 表/索引大小 |
PG_TOTAL_RELATION_SIZE(relname) | 总占用空间 |
OBJ_DESCRIPTION(oid, 'pg_class') | 对象注释 |
11. 其他常用函数
表格
复制
| 类别 | 函数示例 |
|---|---|
| 网络地址 | HOST(inet),MASKLEN(inet),NETWORK(inet) |
| 全文检索 | TO_TSVECTOR(text),TO_TSQUERY(text),TS_RANK() |
| UUID | UUID_GENERATE_V4(),GEN_RANDOM_UUID() |
| 加密 | MD5(text),SHA256(bytea),CRYPT(password, salt) |
| 序列 | NEXTVAL('seq_name'),CURRVAL('seq_name'),LASTVAL() |
PostgreSQL 的内置函数非常丰富，以上只是最常用的部分。如需查看完整列表，建议参考 PostgreSQL 官方文档