PostgreSQL 函数与运算符

PostgreSQL 内置了数百个函数和运算符——字符串处理、日期运算、数学、JSON、聚合、几何……几乎你能想到的操作都有现成函数。本篇精选最常用的几十个,按类别讲解,并重点介绍 NULL 处理和 CASE 表达式这两个日常刚需。

1. 字符串函数

字符串函数可能是日常用得最多的一类。Postgres 的字符串函数完整实现 SQL 标准,且加了不少自己的便捷写法。

-- 字符串函数(最常用的 20 个)
SELECT length('hello');                     -- 5(字符数)
SELECT char_length('你好');                  -- 2(字符数,等价于 length)
SELECT octet_length('你好');                 -- 6(UTF-8 字节数)
SELECT upper('hello');                       -- HELLO
SELECT lower('HELLO');                       -- hello
SELECT initcap('hello world');               -- Hello World(单词首字母大写)

-- 拼接:|| 或 concat()(NULL 友好)
SELECT 'a' || 'b' || 'c';                    -- abc
SELECT 'a' || NULL || 'c';                   -- NULL(任何值 || NULL = NULL)
SELECT concat('a', NULL, 'c');               -- ac(concat 自动跳过 NULL)
SELECT concat_ws('-', '2026', '08', '05');   -- 2026-08-05(带分隔符)

-- 截取与替换
SELECT substring('hello' from 2 for 3);      -- ell(从位置 2 取 3 字符)
SELECT substr('hello', 2, 3);                -- ell(同上的简写)
SELECT left('hello', 3);                     -- hel(左取 3)
SELECT right('hello', 3);                    -- llo(右取 3)
SELECT replace('hello', 'l', 'L');           -- heLLo
SELECT translate('hello', 'el', 'ip');       -- hippo(按字符映射替换)
SELECT lpad('5', 4, '0');                    -- 0005(左补 0 到 4 位)
SELECT rpad('5', 4, '*');                    -- 5***(右补 *)

-- 查找与拆分
SELECT position('l' in 'hello');             -- 3(首次出现位置)
SELECT strpos('hello', 'l');                 -- 3(同上的简写)
SELECT split_part('a,b,c', ',', 2);          -- b(按分隔符取第 N 段)
SELECT trim('  hi  ');                       -- hi(去两端空格)
SELECT btrim('xxabcxx', 'x');                -- abc(去两端指定字符)
SELECT ltrim('/path/', '/');                 -- path/
SELECT reverse('hello');                     -- olleh

-- 模式匹配
SELECT 'hello' LIKE 'h%';                    -- t(LIKE 模式)
SELECT 'hello' ~ '^h[a-z]+$';                -- t(正则匹配,区分大小写)
SELECT 'Hello' ~* '^h';                      -- t(正则,不区分大小写)
SELECT regexp_replace('Hello', 'l', 'L', 'g');  -- HeLlo(全局替换)
SELECT regexp_matches('phone: 12345', '(\\d+)');  -- {12345}
SELECT array_agg(regexp_split_to_array('a,b,c', ','));  -- {{a,b,c}}

三个高频考点:

length vs octet_length:前者是字符数,后者是字节数。中文场景两者差 3 倍。
|| vs concat():|| 遇到 NULL 整个结果是 NULL;concat() 自动跳过 NULL,更安全。
LIKE vs 正则:简单前缀匹配用 LIKE,复杂模式用 ~ / ~*。

2. 日期 / 时间函数

日期函数是做报表、做时间筛选的核心。date_trunc 和 date_part 是两大杀器。

-- 日期 / 时间函数
SELECT now();                                -- 2026-08-05 14:30:00+08(当前完整时间)
SELECT CURRENT_DATE;                         -- 2026-08-05(仅日期)
SELECT CURRENT_TIME;                         -- 14:30:00+08(仅时间)

-- 时间运算(interval 是神器)
SELECT now() + interval '1 day';             -- 明天此刻
SELECT now() - interval '2 hours';           -- 2 小时前
SELECT now() + interval '1 month 3 days';    -- 1 月 3 天后

-- 提取部分
SELECT date_part('year', now());             -- 2026(返回 double)
SELECT date_part('month', now());            -- 8
SELECT EXTRACT(YEAR FROM now());             -- 2026(SQL 标准写法)
SELECT date_part('dow', now());              -- 2(星期几,0=周日,1=周一)

-- 截断到指定精度(做日报/周报/月报必备)
SELECT date_trunc('day', now());             -- 今天 00:00:00
SELECT date_trunc('month', now());           -- 本月 1 号 00:00:00
SELECT date_trunc('year', now());            -- 今年 1 月 1 日 00:00:00

-- 计算年龄 / 差值
SELECT age(now(), '2000-01-01'::date);
-- 26 years 7 mons 4 days 14:30:00
SELECT age('2000-01-01'::date);              -- 等价于 age(now(), ...)
-- 两个时间戳之差(直接相减得到 interval)
SELECT '2026-08-05'::date - '2026-08-01'::date;  -- 4(天数差,整数)

-- 格式化输出
SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS');  -- 2026-08-05 14:30:00
SELECT to_char(now(), 'YYYY"年"MM"月"DD"日"');   -- 2026年08月05日
SELECT to_char(12345.678, 'FM999,999.00');        -- 12,345.68

-- 字符串解析为日期
SELECT to_date('2026-08-05', 'YYYY-MM-DD');
SELECT to_timestamp('2026-08-05 14:30', 'YYYY-MM-DD HH24:MI');

实战技巧:

时间加减:用 interval,不要用秒数运算(可读性差且易错)。
按日/周/月统计:date_trunc('month', created_at) 把时间截断到月初,再 GROUP BY。
中文格式化:用 to_char,把双引号包住的字符当字面量输出。

3. 数学函数

数学函数大多是标准 SQL,Postgres 比较特色的是 Postgres 13+ 加入了 gcd(最大公约数)、lcm(最小公倍数)等。

-- 数学函数
SELECT abs(-5);              -- 5(绝对值)
SELECT ceil(3.2);            -- 4(向上取整)
SELECT floor(3.8);           -- 3(向下取整)
SELECT round(3.14159, 2);    -- 3.14(四舍五入到 2 位)
SELECT trunc(3.14159, 2);    -- 3.14(截断到 2 位,不四舍五入)
SELECT sign(-5);             -- -1(符号函数)
SELECT power(2, 10);         -- 1024(2 的 10 次方)
SELECT sqrt(16);             -- 4(平方根)
SELECT cbrt(27);             -- 3(立方根)
SELECT exp(1);               -- 2.71828...(e 的 n 次方)
SELECT ln(10);               -- 2.302...(自然对数)
SELECT log(100);             -- 2(常用对数,以 10 为底)
SELECT log(2, 8);            -- 3(以 2 为底的对数)
SELECT pi();                 -- 3.14159...(圆周率)
SELECT random();             -- 0~1 之间的随机数
SELECT gcd(12, 18);          -- 6(最大公约数,Postgres 13+)
SELECT lcm(4, 6);            -- 12(最小公倍数,Postgres 13+)

-- 三角函数(弧度制)
SELECT sin(pi()/2);          -- 1
SELECT cos(0);               -- 1
SELECT degrees(pi());        -- 180(弧度转角度)
SELECT radians(180);         -- 3.14159...(角度转弧度)

4. NULL 处理三大神器

NULL 是 SQL 里最反直觉的存在——NULL = NULL 不是 true 而是 NULL。处理 NULL 主要靠下面三个函数:

-- NULL 处理三大神器

-- 1. COALESCE:返回第一个非 NULL 的值
-- 用法:COALESCE(val1, val2, ..., default)
SELECT COALESCE(NULL, NULL, 'fallback');     -- fallback
SELECT COALESCE(nickname, realname, '匿名');  -- 优先取昵称,再取真名,都没有则匿名
-- 报表里把 NULL 替换成 0
SELECT COALESCE(SUM(amount), 0) FROM orders WHERE user_id = 1;

-- 2. NULLIF:两值相等则返回 NULL,否则返回第一个值
-- 用法:NULLIF(a, b)  等价于  CASE WHEN a = b THEN NULL ELSE a END
-- 主要用途:避免除以 0
SELECT NULLIF(count, 0);            -- count 是 0 时返回 NULL
SELECT total / NULLIF(count, 0);    -- count 是 0 时返回 NULL,不报错

-- 3. GREATEST / LEAST:取最大/最小(忽略 NULL)
SELECT GREATEST(1, 5, 3);           -- 5
SELECT LEAST(1, 5, 3);              -- 1
SELECT GREATEST(a, b, c);           -- 三列中的最大值

-- CASE 表达式:SQL 里的 if/else
SELECT
    name,
    CASE
        WHEN age < 18 THEN '未成年'
        WHEN age < 60 THEN '成年'
        ELSE '老年'
    END AS age_group,
    CASE role
        WHEN 'admin' THEN '管理员'
        WHEN 'editor' THEN '编辑'
        ELSE '普通用户'
    END AS role_label
FROM users;

日常最常用的是 COALESCE(把 NULL 替换成默认值)和 NULLIF(把特定值转成 NULL,主要用来防止除零)。CASE WHEN 则是 SQL 里的 if/else,做复杂条件逻辑必备。

5. generate_series — 报表神器

这是 Postgres 的一个杀手级函数——生成连续的整数、日期序列。配合 LEFT JOIN,能完美解决"做日报时缺失的日期怎么补 0"的经典问题。

-- generate_series:Postgres 神器,生成序列
-- 用途:生成连续的整数、日期;做报表时填补"缺失的日期"

-- 1. 整数序列
SELECT * FROM generate_series(1, 5);
-- 1, 2, 3, 4, 5
SELECT * FROM generate_series(0, 10, 2);   -- 步长 2
-- 0, 2, 4, 6, 8, 10

-- 2. 时间序列(日报/月报的填充神器)
SELECT * FROM generate_series(
    '2026-08-01'::date,
    '2026-08-05'::date,
    interval '1 day'
);
-- 2026-08-01 ... 2026-08-05

-- 实战:每天统计订单数,没订单的日子也要显示 0
SELECT
    days.day::date   AS day,
    COUNT(o.id)      AS order_count
FROM generate_series(
    date_trunc('month', now()),
    date_trunc('month', now()) + interval '1 month - 1 day',
    interval '1 day'
) AS days(day)
LEFT JOIN orders o ON date_trunc('day', o.created_at) = days.day
GROUP BY days.day
ORDER BY days.day;

-- 3. 配合数组展开
SELECT * FROM unnest(ARRAY['a', 'b', 'c']) AS letter;
-- a, b, c

上面的"每天订单数"查询是生产环境最常用的模板。MySQL 没有 generate_series,得用递归 CTE 或临时表模拟——Postgres 一个函数搞定。

6. 类型转换

Postgres 类型转换有两种写法:

::(简写,Postgres 特色):'2026-08-05'::date、42::text。最常用。
CAST(标准 SQL):CAST('2026-08-05' AS date)。跨数据库可移植。

常见场景:字符串转日期、字符串转数字、数字转字符串。注意:转换失败会抛错,如果不确定格式,先校验。

7. 自定义函数(PL/pgSQL)

内置函数不够用时,可以用 PL/pgSQL 写自定义函数。本系列不深入展开,但提一下语法骨架:

-- 简单函数:计算两数之和
CREATE OR REPLACE FUNCTION add(a INT, b INT)
RETURNS INT AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT add(3, 5);   -- 8

-- 也可以用更简洁的 SQL 函数(无副作用时)
CREATE OR REPLACE FUNCTION add(a INT, b INT)
RETURNS INT AS $$
    SELECT a + b;
$$ LANGUAGE SQL;

生产环境复杂业务逻辑常封装成数据库函数,好处是网络往返少,坏处是难调试、难版本控制——是否使用看团队取舍。

小结

这一章你认识了 PostgreSQL 最常用的几十个函数:字符串、日期、数学、NULL 处理、generate_series,以及 CASE 表达式。下一篇进入本系列的重头戏——JSONB。

← 上一篇 PostgreSQL SELECT 与 JOIN

下一篇 PostgreSQL JSONB →

✈️💬