PostgreSQL 常用函数（长文讲解）

PostgreSQL 常用函数：从入门到实战的实用指南

在数据库的世界里，PostgreSQL 不仅以强大、稳定著称，更因其丰富的内置函数体系而备受开发者青睐。无论是处理字符串、日期、聚合数据，还是进行复杂的数学运算，PostgreSQL 都提供了丰富且高效的函数支持。对于初学者而言，掌握这些“工具箱”中的核心函数，是提升开发效率、写出更健壮 SQL 的关键一步。

本文将带你系统梳理 PostgreSQL 常用函数，通过真实场景和代码示例，一步步带你从认知到应用，真正把函数变成你写 SQL 时的得力助手。

字符串处理函数：文本的“雕刻刀”

字符串是数据库中最常见的数据类型之一。在用户表、日志记录、商品描述等场景中，我们经常需要对文本进行截取、拼接、替换、大小写转换等操作。PostgreSQL 提供了非常强大的字符串函数库。

常用字符串函数示例

-- 取前 5 个字符，适合截取用户名、标题等
SELECT LEFT('Hello World', 5) AS result; -- 返回 'Hello'

-- 取后 5 个字符，常用于提取文件扩展名
SELECT RIGHT('example.txt', 4) AS result; -- 返回 'txt'

-- 字符串拼接，使用 || 操作符（PostgreSQL 专用）
SELECT '你好' || '，世界！' AS greeting; -- 返回 '你好，世界！'

-- 替换子字符串，比如修复拼写错误
SELECT REPLACE('PostgreSQL 是个好数据库', '好', '优秀') AS fixed_text;
-- 返回 'PostgreSQL 是个优秀数据库'

-- 去除首尾空格，常用于用户输入清洗
SELECT TRIM('   欢迎光临   ') AS cleaned_text; -- 返回 '欢迎光临'

-- 转为大写或小写，适合统一格式
SELECT UPPER('hello postgres') AS upper_case; -- 返回 'HELLO POSTGRES'
SELECT LOWER('HELLO POSTGRES') AS lower_case; -- 返回 'hello postgres'

💡 小贴士：|| 是 PostgreSQL 中字符串拼接的专用符号，与 MySQL 的 CONCAT 或 SQL Server 的 + 不同，用起来更简洁。想象它就像“胶水”，把两段文字粘在一起。

日期与时间函数：时间的“导航仪”

在订单系统、日志分析、用户行为追踪中，时间处理是高频需求。PostgreSQL 对时间类型的支持非常全面，内置函数能轻松应对各种场景。

时间函数实战案例

-- 获取当前时间戳（包含时区）
SELECT NOW() AS current_time; -- 返回类似 '2025-04-05 10:30:45.123456+08'

-- 获取当前日期（不带时间）
SELECT CURRENT_DATE AS today; -- 返回 '2025-04-05'

-- 计算未来 7 天的时间
SELECT NOW() + INTERVAL '7 days' AS next_week;

-- 计算两个日期之间的天数差
SELECT DATE_PART('day', '2025-04-12'::DATE - '2025-04-05'::DATE) AS days_diff;
-- 返回 7

-- 提取年、月、日等部分
SELECT EXTRACT(YEAR FROM '2025-04-05'::DATE) AS year; -- 返回 2025
SELECT EXTRACT(MONTH FROM '2025-04-05'::DATE) AS month; -- 返回 4
SELECT EXTRACT(DAY FROM '2025-04-05'::DATE) AS day; -- 返回 5

-- 格式化输出时间（类似 strftime）
SELECT TO_CHAR(NOW(), 'YYYY年MM月DD日 HH24:MI') AS formatted_time;
-- 返回 '2025年04月05日 10:30'

🌟 场景应用：在电商系统中，判断订单是否超过 7 天未发货，可以用 NOW() - INTERVAL '7 days' 与 created_at 字段比较，非常直观。

数学与聚合函数：数据的“计算器”

在统计报表、数据分析、财务计算中，数学函数和聚合函数是核心工具。PostgreSQL 提供了从基础运算到复杂统计的完整支持。

数学与聚合函数示例

-- 基础数学运算
SELECT ABS(-100) AS absolute_value; -- 返回 100
SELECT ROUND(3.14159, 2) AS rounded; -- 返回 3.14
SELECT CEIL(3.1) AS ceiling; -- 返回 4（向上取整）
SELECT FLOOR(3.9) AS floor; -- 返回 3（向下取整）

-- 聚合函数：计算总和、平均值、最大最小值
SELECT 
    SUM(salary) AS total_salary,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM employees;

-- 计算数量（不包含 NULL）
SELECT COUNT(*) AS total_records FROM employees;
SELECT COUNT(salary) AS non_null_salary_count FROM employees;

-- 分组统计：按部门统计平均薪资
SELECT 
    department,
    AVG(salary) AS avg_dept_salary,
    COUNT(*) AS emp_count
FROM employees
GROUP BY department
ORDER BY avg_dept_salary DESC;

📊 理解聚合函数：它们就像“数据筛子”——把原始数据按规则“筛”出汇总结果。GROUP BY 是筛子的“筛网”，SUM、AVG 等是“筛出的颗粒”。

数组与 JSON 函数：复杂数据的“容器”

PostgreSQL 对数组和 JSON 的支持非常强大，使其不仅能当传统数据库用，还能胜任 NoSQL 场景。这在现代应用中尤其重要。

数组操作函数示例

-- 创建数组并初始化
SELECT ARRAY[1, 2, 3, 4] AS numbers; -- 返回 {1,2,3,4}

-- 检查数组是否包含某个值
SELECT 2 = ANY(ARRAY[1, 2, 3]) AS contains_two; -- 返回 true

-- 数组长度
SELECT ARRAY_LENGTH(ARRAY['a','b','c'], 1) AS length; -- 返回 3

-- 数组转字符串（用逗号分隔）
SELECT ARRAY_TO_STRING(ARRAY['苹果', '香蕉', '橙子'], ',') AS fruits; -- 返回 '苹果,香蕉,橙子'

-- 从字符串创建数组
SELECT STRING_TO_ARRAY('红,黄,蓝', ',') AS colors; -- 返回 {红,黄,蓝}

JSON 处理函数示例

-- 创建 JSON 对象
SELECT '{"name": "张三", "age": 25}'::json AS user_info;

-- 提取 JSON 字段值
SELECT 
    '{"name": "李四", "city": "北京"}'::json->>'name' AS name,
    '{"name": "李四", "city": "北京"}'::json->>'city' AS city;

-- 检查 JSON 是否包含某键
SELECT '{"a": 1, "b": 2}'::json ? 'a' AS has_key_a; -- 返回 true

-- 更新 JSON 字段（部分更新）
SELECT 
    '{"name": "王五", "age": 30}'::json || '{"age": 31, "job": "工程师"}' AS updated_json;
-- 返回 {"name": "王五", "age": 31, "job": "工程师"}

🧩 比喻说明：数组像“购物车”，JSON 像“用户档案”。PostgreSQL 让你既能管理购物车里的商品，也能读写用户的完整信息，无需额外建表。

条件与流程控制函数：逻辑的“开关”

在 SQL 中，我们常需要根据条件返回不同值，或处理 NULL。PostgreSQL 提供了 CASE 表达式、COALESCE、NULLIF 等函数，让逻辑表达更灵活。

条件函数实战

-- CASE 表达式：根据薪资分级
SELECT 
    name,
    salary,
    CASE 
        WHEN salary < 5000 THEN '初级'
        WHEN salary BETWEEN 5000 AND 10000 THEN '中级'
        ELSE '高级'
    END AS level
FROM employees;

-- COALESCE：返回第一个非空值，常用于处理 NULL
SELECT COALESCE(NULL, '默认值', '备用') AS result; -- 返回 '默认值'

-- NULLIF：若两值相等则返回 NULL，否则返回第一个
SELECT NULLIF(5, 5) AS same, NULLIF(5, 6) AS different;
-- 返回 NULL 和 5

-- IFNULL（PostgreSQL 无此函数，但可用 COALESCE 模拟）
SELECT COALESCE(user_name, '匿名用户') AS display_name FROM users;

✅ 最佳实践：COALESCE 比 ISNULL 更灵活，支持多个参数，是处理缺失数据的首选。

总结：掌握 PostgreSQL 常用函数，提升 SQL 实战力

PostgreSQL 常用函数并非孤立的工具，而是构建复杂逻辑的“积木”。从字符串处理到时间计算，从数学统计到 JSON 操作，每一种函数都在解决特定问题。

作为开发者，我们不应机械记忆，而应理解其应用场景。比如，TRIM 不只是去空格，更是数据清洗的第一步；COALESCE 不只是处理 NULL，更是保障程序健壮性的关键。

当你在写 SQL 时，不妨多问一句：“有没有现成的函数能帮我解决这个问题？”——答案很可能是：有，而且它就在 PostgreSQL 的函数库里。

无论你是初学者还是中级开发者，熟练掌握这些函数，都能让你的 SQL 代码更简洁、更高效、更专业。从今天起，让 PostgreSQL 常用函数成为你写 SQL 时的“隐形助手”。