PostgreSQL 常用函数

下面是一份 PostgreSQL 常用函数 的 完整实战手册，涵盖 字符串、数字、日期、JSON、数组、聚合、窗口、条件、转换、系统、数学、加密、模糊匹配 等 13 大类，共 100+ 函数，带 分类、语法、示例、性能提示、最佳实践与面试重点，适合开发、DBA、架构师使用。

目录（快速跳转）

类别	函数数量	跳转
1. 字符串函数	20+	点击
2. 数字函数	15+	点击
3. 日期时间函数	20+	点击
4. JSON 函数	15+	点击
5. 数组函数	12+	点击
6. 聚合函数	10+	点击
7. 窗口函数	8+	点击
8. 条件判断函数	6+	点击
9. 类型转换函数	8+	点击
10. 系统信息函数	10+	点击
11. 数学函数	10+	点击
12. 加密与哈希	6+	点击
13. 模糊匹配与扩展	6+	点击

1. 字符串函数

函数	说明	示例
`CONCAT(str1, ...)`	连接字符串（忽略 NULL）	`CONCAT('A', 'B') → 'AB'`
`\|\|`	字符串拼接	`'A' \|\| 'B' → 'AB'`
`CONCAT_WS(sep, ...)`	带分隔符拼接	`CONCAT_WS('-', 'A', 'B') → 'A-B'`
`LEFT(str, n)`	左起 n 位	`LEFT('ABC', 2) → 'AB'`
`RIGHT(str, n)`	右起 n 位	`RIGHT('ABC', 2) → 'BC'`
`SUBSTRING(str FROM n FOR m)`	截取	`SUBSTRING('ABCDE' FROM 2 FOR 3) → 'BCD'`
`TRIM([LEADING\|TRAILING\|BOTH] chars FROM str)`	去空格/字符	`TRIM(' ABC ') → 'ABC'`
`BTRIM`, `LTRIM`, `RTRIM`	同上
`REPLACE(str, from, to)`	替换	`REPLACE('AABB', 'A', 'X') → 'XXBB'`
`REGEXP_REPLACE(str, pattern, repl)`	正则替换	`REGEXP_REPLACE('abc123', '\d+', 'XXX') → 'abcXXX'`
`UPPER(str)`, `LOWER(str)`	大小写	`UPPER('abc') → 'ABC'`
`INITCAP(str)`	首字母大写	`INITCAP('hello world') → 'Hello World'`
`LENGTH(str)`	长度（字符）	`LENGTH('中文') → 2`
`CHAR_LENGTH(str)`	同上
`BIT_LENGTH(str)`	位长度	`BIT_LENGTH('ABC') → 24`
`OVERLAY(str PLACING new FROM pos [FOR len])`	替换子串	`OVERLAY('ABCDE' PLACING 'XX' FROM 2 FOR 2) → 'AXXDE'`
`POSITION(substr IN str)`	查找位置	`POSITION('B' IN 'ABC') → 2`
`STRPOS(str, substr)`	同上
`SPLIT_PART(str, delim, n)`	按分隔符拆分	`SPLIT_PART('A,B,C', ',', 2) → 'B'`
`REVERSE(str)`	反转	`REVERSE('ABC') → 'CBA'`

2. 数字函数

函数	说明	示例
`ABS(n)`	绝对值	`ABS(-5) → 5`
`ROUND(n, d)`	四舍五入	`ROUND(3.14159, 2) → 3.14`
`CEIL(n)`, `FLOOR(n)`	向上/向下取整	`CEIL(3.1) → 4`
`TRUNC(n, d)`	截断	`TRUNC(3.14159, 2) → 3.14`
`MOD(n, m)`	取模	`MOD(10, 3) → 1`
`POWER(n, p)`	幂	`POWER(2, 3) → 8`
`SQRT(n)`	平方根	`SQRT(16) → 4`
`LN(n)`, `LOG(b, n)`	对数	`LN(2.718) ≈ 1`
`EXP(n)`	e^n	`EXP(1) ≈ 2.718`
`PI()`	π	`PI() → 3.14159...`
`RANDOM()`	0~1 随机数	`RANDOM()`
`SETSEED(seed)`	设置随机种子	`SETSEED(0.5)`
`GREATEST(n1, n2, ...)`	最大值	`GREATEST(1,5,3) → 5`
`LEAST(n1, n2, ...)`	最小值	`LEAST(1,5,3) → 1`

3. 日期时间函数

函数	说明	示例
`NOW()`	当前时间（事务开始）	`NOW()`
`CURRENT_DATE`, `CURRENT_TIME`	日期/时间
`DATE_TRUNC('unit', ts)`	截断	`DATE_TRUNC('day', NOW())`
`EXTRACT(field FROM ts)`	提取部分	`EXTRACT(MONTH FROM NOW())`
`DATE_PART('field', ts)`	同上
`AGE(ts1, ts2)`	时间差	`AGE(NOW(), '2000-01-01')`
`TO_CHAR(ts, 'format')`	格式化	`TO_CHAR(NOW(), 'YYYY-MM-DD')`
`TO_TIMESTAMP(str, 'format')`	解析	`TO_TIMESTAMP('2025/04/05', 'YYYY/MM/DD')`
`INTERVAL 'n unit'`	间隔	`INTERVAL '3 days'`
`MAKE_DATE(y,m,d)`	构造日期	`MAKE_DATE(2025,4,5)`
`MAKE_TIME(h,m,s)`	构造时间
`JUSTIFY_DAYS(interval)`	规范化	`JUSTIFY_DAYS(INTERVAL '35 days') → 1 mon 5 days`

4. JSON 函数

函数	说明	示例
`->`	取对象字段	`data->'name'`
`->>`	取字段为文本	`data->>'name'`
`#>`	按路径取对象	`data #> '{user, name}'`
`#>>`	按路径取文本	`data #>> '{user, name}'`
`json_array_elements(json)`	展开数组
`json_object_keys(json)`	获取键
`json_array_length(json)`	数组长度
`jsonb_set(jsonb, path, new_val)`	设置值（不可变）
`jsonb_insert(...)`	插入值
`jsonb_build_object(...)`	构造对象	`jsonb_build_object('id', 1, 'name', 'Alice')`
`jsonb_build_array(...)`	构造数组
`jsonb_agg(expr)`	聚合为 JSON 数组
`jsonb_object_agg(key, val)`	聚合为 JSON 对象

5. 数组函数

函数	说明	示例
`ARRAY[1,2,3]`	构造数组
`unnest(arr)`	展开数组	`SELECT unnest(ARRAY[1,2,3])`
`array_append(arr, elem)`	添加元素
`array_prepend(elem, arr)`	前插
`array_remove(arr, elem)`	删除元素
`array_replace(arr, old, new)`	替换
`array_position(arr, elem)`	查找位置
`array_length(arr, dim)`	长度
`array_to_string(arr, delim)`	转字符串	`array_to_string(ARRAY[1,2], ',') → '1,2'`
`string_to_array(str, delim)`	转数组
`array_agg(expr)`	聚合为数组

6. 聚合函数

函数	说明	示例
`COUNT(*)`, `COUNT(col)`	计数
`SUM(col)`, `AVG(col)`	求和/平均
`MIN(col)`, `MAX(col)`	最小/最大
`STRING_AGG(expr, delim)`	字符串拼接	`STRING_AGG(name, ', ')`
`ARRAY_AGG(expr)`	数组聚合
`JSON_AGG(expr)`	JSON 数组
`JSONB_AGG(expr)`	JSONB 数组
`BOOL_AND(expr)`	全真为真
`BOOL_OR(expr)`	任一真为真

7. 窗口函数

函数	说明	示例
`ROW_NUMBER()`	行号
`RANK()`	排名（跳跃）
`DENSE_RANK()`	排名（连续）
`NTILE(n)`	分桶
`LAG(col, n)`	上 n 行值
`LEAD(col, n)`	下 n 行值
`FIRST_VALUE(col)`	窗口首值
`LAST_VALUE(col)`	窗口末值

SELECT 
    user_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn
FROM orders;

8. 条件判断函数

函数	说明	示例
`COALESCE(v1, v2, ...)`	第一个非 NULL	`COALESCE(phone, 'N/A')`
`NULLIF(v1, v2)`	v1=v2 → NULL	`NULLIF(status, 'deleted')`
`GREATEST(...)`, `LEAST(...)`	最大/最小
`CASE WHEN ... THEN ... ELSE ... END`	条件分支
`DECODE(str, 'search', 'result', ...)`	类似 Oracle

9. 类型转换函数

函数	说明	示例
`::type`	强制转换	`'123'::INT`
`CAST(expr AS type)`	标准转换	`CAST('123' AS INTEGER)`
`TO_CHAR(...)`	时间/数字 → 字符串
`TO_TIMESTAMP(...)`	字符串 → 时间
`TO_NUMBER(str, 'format')`	字符串 → 数字	`TO_NUMBER('12,345.67', '99G999D99')`
`TO_DATE(str, 'format')`	字符串 → 日期

10. 系统信息函数

函数	说明	示例
`CURRENT_USER`, `SESSION_USER`	当前用户
`CURRENT_DATABASE()`	当前数据库
`CURRENT_SCHEMA()`	当前 schema
`VERSION()`	PostgreSQL 版本
`PG_SIZE_PRETTY(bytes)`	字节美化	`PG_SIZE_PRETTY(1024) → '1 kB'`
`PG_RELATION_SIZE(rel)`	表大小
`PG_TOTAL_RELATION_SIZE(rel)`	含索引
`INET_CLIENT_ADDR()`	客户端 IP
`PG_BACKEND_PID()`	当前进程 ID

11. 数学函数

函数	说明
`SIN`, `COS`, `TAN`, `ASIN`, `ACOS`	三角函数
`DEGREES(rad)`, `RADIANS(deg)`	角度弧度互转
`WIDTH_BUCKET(val, min, max, buckets)`	分桶

12. 加密与哈希

函数	说明
`MD5(str)`	MD5 哈希
`PG_CRYPTO` 扩展：`encrypt()`, `decrypt()`, `gen_salt()`, `crypt()`	加密
`HMAC(data, key, 'sha256')`	HMAC

-- 安装加密扩展
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- 密码加密
SELECT crypt('password123', gen_salt('bf'));

13. 模糊匹配与扩展

函数/扩展	说明
`pg_trgm`	三元组相似度
`SIMILARITY(str1, str2)`	相似度（0~1）
`WORD_SIMILARITY()`	单词级相似
`FUZZYSTRMATCH`	`soundex`, `levenshtein`
`FULL TEXT SEARCH`	`to_tsvector`, `to_tsquery`, `@@`

-- 安装扩展
CREATE EXTENSION pg_trgm;
CREATE EXTENSION fuzzystrmatch;

-- 模糊搜索
SELECT * FROM users 
WHERE name % 'Jhon';  -- 类似 John

最佳实践速查表

场景	推荐函数
拼接字符串	`CONCAT_WS(',', ...)`
防 NULL	`COALESCE(col, 'N/A')`
分页排名	`ROW_NUMBER() OVER (...)`
JSON 取值	`->>`
数组展开	`unnest()`
字符串聚合	`STRING_AGG(name, ', ')`
密码存储	`crypt(?, gen_salt('bf'))`
模糊搜索	`pg_trgm` + `%`
安全转换	`CAST(col AS TYPE)`

性能提示

场景	避免	推荐
大量拼接	`\|\|` 循环	`STRING_AGG`
JSON 频繁修改	`\|\|` 合并	`jsonb_set`
数组查找	`arr @> ARRAY[val]`	建 `GIN` 索引
模糊查询	`LIKE '%abc%'`	`pg_trgm` + `GIN`
类型转换	隐式转换	显式 `CAST`

完整示例：用户行为分析

SELECT 
    u.id,
    u.email,
    COALESCE(u.profile->>'name', '匿名') AS name,
    COUNT(o.id) AS order_count,
    STRING_AGG(DISTINCT p.category, ', ') AS categories,
    ARRAY_AGG(DISTINCT o.status) AS status_list,
    ROUND(AVG(o.amount), 2) AS avg_amount,
    ROW_NUMBER() OVER (PARTITION BY u.city ORDER BY COUNT(o.id) DESC) AS rank_in_city
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
WHERE o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY u.id, u.email
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC
LIMIT 10;

面试高频题

问题	答案
`COALESCE` 和 `NULLIF` 区别？	前者取非空，后者相等返回 NULL
如何实现 `IFNULL`？	`COALESCE(col, default)`
`STRING_AGG` 有序吗？	默认无序，加 `ORDER BY`
JSON 字段建索引？	`CREATE INDEX ON table USING GIN (json_col)`
数组包含查询？	`WHERE tags @> ARRAY['vip']`

需要我提供一个 “函数速查 PDF” 或 “SQL 模板库（含 50 个业务场景）” 吗？

目录（快速跳转）

1. 字符串函数

2. 数字函数

3. 日期时间函数

4. JSON 函数

5. 数组函数

6. 聚合函数

7. 窗口函数

8. 条件判断函数

9. 类型转换函数

10. 系统信息函数

11. 数学函数

12. 加密与哈希

13. 模糊匹配与扩展

最佳实践速查表

性能提示

完整示例：用户行为分析

面试高频题

PostgreSQL SELECT 语句

PostgreSQL WHERE 子句

PostgreSQL View（视图）

PostgreSQL WITH 子句

Linux 上安装 PostgreSQL

PostgreSQL ORDER BY 语句

发表回复取消回复

目录（快速跳转）

1. 字符串函数

2. 数字函数

3. 日期时间函数

4. JSON 函数

5. 数组函数

6. 聚合函数

7. 窗口函数

8. 条件判断函数

9. 类型转换函数

10. 系统信息函数

11. 数学函数

12. 加密与哈希

13. 模糊匹配与扩展

最佳实践速查表

性能提示

完整示例：用户行为分析

面试高频题

类似文章

发表回复 取消回复

发表回复取消回复