PostgreSQL 提供了大量的内建函数，这些函数支持各种常见的数据处理任务，帮助开发者完成从字符串操作到数据类型转换等各种操作。以下是 PostgreSQL 常用函数的详细分类和使用介绍：

1. 数学函数

1.1 ABS(x)

返回数值 x 的绝对值。

SELECT ABS(-10);  -- 返回 10

1.2 CEIL(x) 或 CEILING(x)

返回大于或等于 x 的最小整数。

SELECT CEIL(4.3);  -- 返回 5

1.3 FLOOR(x)

返回小于或等于 x 的最大整数。

SELECT FLOOR(4.7);  -- 返回 4

1.4 ROUND(x, n)

将数字 x 四舍五入到小数点后 n 位。

SELECT ROUND(4.5678, 2);  -- 返回 4.57

1.5 POWER(x, y)

返回 x 的 y 次幂。

SELECT POWER(2, 3);  -- 返回 8

1.6 SQRT(x)

返回数字 x 的平方根。

SELECT SQRT(16);  -- 返回 4

1.7 RANDOM()

返回一个介于 0 和 1 之间的随机浮动数值。

SELECT RANDOM();  -- 返回一个随机值，如 0.456789

1.8 PI()

返回常数 π 的值。

SELECT PI();  -- 返回 3.14159265358979

---

2. 字符串函数

2.1 CONCAT(str1, str2, ...)

连接多个字符串。

SELECT CONCAT('Hello', ' ', 'World');  -- 返回 'Hello World'

2.2 LENGTH(str)

返回字符串 str 的长度。

SELECT LENGTH('Hello');  -- 返回 5

2.3 LOWER(str)

将字符串 str 转为小写。

SELECT LOWER('Hello');  -- 返回 'hello'

2.4 UPPER(str)

将字符串 str 转为大写。

SELECT UPPER('Hello');  -- 返回 'HELLO'

2.5 TRIM([LEADING | TRAILING | BOTH] trim_character FROM str)

移除字符串两端的空格或指定字符。

SELECT TRIM(' ' FROM '  Hello  ');  -- 返回 'Hello'

2.6 REPLACE(str, old_substring, new_substring)

在字符串 str 中替换 old_substring 为 new_substring。

SELECT REPLACE('Hello World', 'World', 'PostgreSQL');  -- 返回 'Hello PostgreSQL'

2.7 POSITION(substring IN str)

返回子字符串 substring 在字符串 str 中的位置。

SELECT POSITION('l' IN 'Hello');  -- 返回 3

2.8 SUBSTRING(str FROM start FOR length)

返回从 str 开始的指定长度的子字符串。

SELECT SUBSTRING('Hello' FROM 2 FOR 3);  -- 返回 'ell'

2.9 REVERSE(str)

返回字符串的反转。

SELECT REVERSE('Hello');  -- 返回 'olleH'

---

3. 日期和时间函数

3.1 CURRENT_DATE

返回当前日期。

SELECT CURRENT_DATE;  -- 返回 '2025-03-12'

3.2 CURRENT_TIME

返回当前时间。

SELECT CURRENT_TIME;  -- 返回 '14:30:45'

3.3 CURRENT_TIMESTAMP

返回当前时间戳（包括日期和时间）。

SELECT CURRENT_TIMESTAMP;  -- 返回 '2025-03-12 14:30:45'

3.4 NOW()

返回当前时间戳（与 CURRENT_TIMESTAMP 相同）。

SELECT NOW();  -- 返回 '2025-03-12 14:30:45'

3.5 AGE(timestamp, timestamp)

返回两个时间戳之间的间隔。

SELECT AGE('2025-03-12', '1990-01-01');  -- 返回 '35 years 2 mons 11 days'

3.6 EXTRACT(field FROM source)

从日期或时间中提取指定部分（如年、月、日、小时等）。

SELECT EXTRACT(YEAR FROM CURRENT_DATE);  -- 返回当前年份

3.7 TO_DATE(string, format)

将字符串转换为日期。

SELECT TO_DATE('2025-03-12', 'YYYY-MM-DD');  -- 返回 '2025-03-12'

3.8 TO_TIMESTAMP(string, format)

将字符串转换为时间戳。

SELECT TO_TIMESTAMP('2025-03-12 14:30:45', 'YYYY-MM-DD HH24:MI:SS');  -- 返回 '2025-03-12 14:30:45'

3.9 DATE_TRUNC(field, source)

将日期或时间戳截断到指定的粒度。

SELECT DATE_TRUNC('year', CURRENT_DATE);  -- 返回 '2025-01-01 00:00:00'

---

4. JSON 和 JSONB 函数

4.1 JSONB_BUILD_OBJECT(key, value, ...)

创建一个 JSONB 对象。

SELECT JSONB_BUILD_OBJECT('name', 'John', 'age', 30);  -- 返回 '{"name": "John", "age": 30}'

4.2 JSONB_ARRAY_LENGTH(jsonb)

返回 JSONB 数组的长度。

SELECT JSONB_ARRAY_LENGTH('[1, 2, 3]'::jsonb);  -- 返回 3

4.3 JSONB_SET(jsonb, path, new_value)

修改 JSONB 对象中的某个值。

SELECT JSONB_SET('{"name": "John", "age": 30}'::jsonb, '{age}', '35');  -- 返回 '{"name": "John", "age": 35}'

---

5. 聚合函数

5.1 COUNT(*)

返回符合条件的行数。

SELECT COUNT(*) FROM employees;  -- 返回员工总数

5.2 SUM(column)

返回某列的总和。

SELECT SUM(salary) FROM employees;  -- 返回所有员工的工资总和

5.3 AVG(column)

返回某列的平均值。

SELECT AVG(salary) FROM employees;  -- 返回员工的平均工资

5.4 MIN(column)

返回某列的最小值。

SELECT MIN(salary) FROM employees;  -- 返回最低工资

5.5 MAX(column)

返回某列的最大值。

SELECT MAX(salary) FROM employees;  -- 返回最高工资

5.6 GROUP_CONCAT()

返回一个字符串，它由组内每一行的值连接而成。

SELECT STRING_AGG(name, ', ') FROM employees;  -- 返回所有员工姓名的逗号分隔列表

---

6. 条件函数

6.1 COALESCE(value, ...)

返回列表中的第一个非 NULL 值。

SELECT COALESCE(NULL, NULL, 'First', 'Second');  -- 返回 'First'

6.2 NULLIF(value1, value2)

如果两个值相等，返回 NULL，否则返回第一个值。

SELECT NULLIF(10, 10);  -- 返回 NULL
SELECT NULLIF(10, 5);   -- 返回 10

6.3 CASE

条件语句，根据条件返回不同的值。

SELECT CASE WHEN salary > 50000 THEN 'High' ELSE 'Low' END FROM employees;

---

7. 字符串转义和处理

7.1 MD5(string)

返回字符串的 MD5 哈希值。

SELECT MD5('password');  -- 返回 '5f4dcc3b5aa765d61d8327deb882cf99'

7.2 ENCODE(bytea, encoding)

将二进制数据编码为指定格式（如 base64）。

SELECT ENCODE('Hello'::bytea, 'base64');  -- 返回 'SGVsbG8='

7.3 DECODE(string, encoding)

将编码后的字符串解码。

SELECT DECODE('SGVsbG8='::text, 'base64');  -- 返回 'Hello'

---

总结

PostgreSQL 的函数功能非常强大，涵盖了从数据类型处理、数学计算、字符串操作、日期时间、聚合函数、JSON 数据处理等多方面的需求。了解并掌握这些函数能有效提高数据查询和管理效率。更多详细内容请关注其他相关文章！