PostgreSQL 内置函数

下面是一份超系统级的 PostgreSQL 内置函数大全（附分类 + 示例），覆盖 PostgreSQL 17 时代常用的所有函数类型，重点突出实际开发中最常使用的部分。

内容分为：

🧊 核心函数（必会）
🔧 字符串函数
🕒 日期与时间函数
🔢 数学函数
📦 聚合函数
🪞 窗口函数
🧬 JSON 函数
🧱 数组函数
🏗️ 类型转换函数
📚 系统信息函数
🔐 加密与哈希函数
🧩 网络与 IP 相关函数
🔀 其他特殊函数（正则、范围、几何）

🧊 1. 核心通用函数（最常用 TOP 级）

COALESCE(value, …)

返回第一个非 NULL 值。

SELECT COALESCE(null, null, 5, 9);  -- 5

NULLIF(a, b)

相等时返回 NULL。

SELECT NULLIF(10,10);  -- NULL

GREATEST(a, b, …) / LEAST(a, b, …)

SELECT GREATEST(5, 9, 1); -- 9

ROUND(x, n)

保留小数：

SELECT ROUND(123.456, 2); -- 123.46

CAST / ::

SELECT '123'::int;

🔧 2. 字符串函数

LENGTH(s)

长度：

SELECT LENGTH('hello'); -- 5

LOWER(s) / UPPER(s)

SELECT LOWER('Hello'); -- hello

TRIM / LTRIM / RTRIM

SELECT TRIM('   ab cd  '); -- 'ab cd'

SUBSTRING(s, from, len)

SELECT SUBSTRING('abcdef', 2, 3); -- 'bcd'

REPLACE(s, from, to)

SELECT REPLACE('abcabc','a','X'); -- XbcXbc

POSITION(substr IN str)

SELECT POSITION('cd' IN 'abcde'); -- 3

CONCAT(…) / ||

SELECT 'a' || 'b'; -- ab

STRING_AGG(col, sep)

（聚合）

SELECT STRING_AGG(name, ',') FROM users;

正则相关

REGEXP_MATCHES, REGEXP_REPLACE, REGEXP_SPLIT_TO_ARRAY

🕒 3. 日期与时间函数

当前时间

SELECT NOW();
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_DATE;

时间加减

SELECT NOW() + INTERVAL '2 days';

EXTRACT(field FROM date)

SELECT EXTRACT(YEAR FROM NOW());

DATE_TRUNC(field, timestamp)

按年月日时区间截断：

SELECT DATE_TRUNC('month', NOW());

年龄计算

SELECT AGE('2025-01-01', '2000-01-01'); -- 25 years

🔢 4. 数学函数

ABS(x)、CEIL(x)、FLOOR(x)

POWER(a, b)、SQRT(x)

RANDOM()（0-1）

SELECT FLOOR(RANDOM() * 100);

📦 5. 聚合函数（Aggregates）

COUNT(*)

SELECT COUNT(*) FROM users;

SUM(col) / AVG(col) / MIN / MAX

SELECT AVG(age) FROM users;

JSON_AGG / ARRAY_AGG

SELECT ARRAY_AGG(id) FROM users;

🪞 6. 窗口函数（Window Functions）

ROW_NUMBER() OVER (…)

SELECT ROW_NUMBER() OVER (ORDER BY id) FROM users;

RANK() / DENSE_RANK()

LAG() / LEAD() 前后行取值

SELECT LAG(amount) OVER (ORDER BY id) FROM orders;

SUM() OVER

累计求和：

SELECT SUM(amount) OVER (ORDER BY id) FROM orders;

🧬 7. JSON / JSONB 函数（PostgreSQL 超强）

获取键值

SELECT data->'name' FROM users_json;
SELECT data->>'name';  -- text

合并对象

SELECT jsonb '{"a":1}' || '{"b":2}';

遍历键值

SELECT jsonb_each(data) FROM users_json;

json_agg() 聚合行成为 JSON 数组

SELECT json_agg(u)
FROM (SELECT id,name FROM users LIMIT 3) u;

🧱 8. 数组函数

数组长度

SELECT array_length(tags, 1) FROM articles;

unnest() 展开为多行

SELECT unnest(tags) FROM articles;

数组是否包含

SELECT '{1,2,3}'::int[] @> '{2}';

🏗️ 9. 类型转换函数 / 类型检查

TO_CHAR(number_or_date, format)

SELECT TO_CHAR(NOW(),'YYYY-MM-DD HH24:MI:SS');

TO_TIMESTAMP(epoch)

SELECT TO_TIMESTAMP(1700000000);

pg_typeof(x)

SELECT pg_typeof(123); -- integer

📚 10. 系统信息函数

当前用户 / 数据库 / schema

SELECT CURRENT_USER;
SELECT CURRENT_DATABASE();
SELECT CURRENT_SCHEMA();

查看当前查询

SELECT pg_backend_pid();
SELECT pg_stat_activity;

获取列表：

SELECT * FROM pg_tables;
SELECT * FROM pg_indexes;

🔐 11. 加密 / 哈希函数

md5(text)

SELECT md5('abc');

pgcrypto 扩展（推荐）

先安装：

CREATE EXTENSION pgcrypto;

常用：

SELECT crypt('password', gen_salt('bf'));  -- hash
SELECT digest('abc', 'sha256');             -- binary
SELECT encode(digest('abc','sha256'),'hex');

🌐 12. 网络 / IP 函数

PostgreSQL 原生支持 CIDR / INET 类型。

判断 IP 是否属于网段

SELECT '192.168.1.8'::inet << '192.168.1.0/24';

取子网、广播地址

SELECT broadcast('192.168.1.10/24'::inet);
SELECT netmask('192.168.1.10/24'::inet);

🔀 13. 其他特殊内置函数

正则

SELECT 'abc123' ~ '[0-9]';  -- true

范围类型函数

SELECT int4range(1,10) @> 5;

几何函数

SELECT area(box(point(0,0), point(10,10)));