PostgreSQL常用函数实战：从字符串到JSON与窗口函数完全指南

1. 前言PostgreSQL 的常用函数说实话是很多人从 MySQL 迁过来之后第一个要重新适应的东西。语法有些相似但细节差异很大尤其是类型转换、字符串处理、JSON 操作这几个方向。这篇文章不打算照搬官方文档而是按我平时写 SQL 的实际习惯把最常用的函数过一遍顺便把那些容易踩坑的地方点出来。我从 MySQL 转到 PostgreSQL 大概两三年期间做过不少数据迁移和报表开发。刚开始确实不适应比如字符串拼接的写法、类型转换的语法、数组和 JSON 的处理方式都跟 MySQL 不太一样。但用熟了之后反而觉得 PostgreSQL 的函数生态更强尤其适合做复杂查询和数据分析。这篇文章适合正在学 PostgreSQL 的开发者、从 MySQL 迁过来的同学以及平时要写报表 SQL 的数据分析师。我会按功能分类讲每类给一些直接能用的示例也会说明哪些地方容易出错。2. 字符串函数写业务 SQL 最离不开的模块字符串处理在业务查询里极其常见无论是格式化输出、拼接字段还是清洗数据都会用到。PostgreSQL 的字符串函数比 MySQL 丰富但写法上有不少需要注意的差异。2.1 拼接、截取、替换日常操作PostgreSQL 里拼接字符串有两种方式一种是直接用||运算符另一种是用concat函数。两种我都在用但推荐优先用concat因为遇到 NULL 值时不会整个变成 NULL。这一点很多从 MySQL 过来的人会踩坑因为 MySQL 的CONCAT函数遇到 NULL 会返回 NULL而 PostgreSQL 的concat会把 NULL 当作空字符串。-- MySQL 习惯的写法可能这样 SELECT Hello || , || World; -- 返回 Hello, World -- 但如果有 NULL 参与 SELECT Hello || NULL; -- 返回 NULL注意 -- 用 concat 则安全很多 SELECT concat(Hello, , , World); -- Hello, World SELECT concat(Hello, NULL); -- Hello截取字符串用substringPostgreSQL 支持三种用法标准 SQL 的substring(str from start for count)风格也有substr(str, start, count)风格。我平时用substr更多函数名短参数直接。注意两个函数的起始位置都是从 1 开始不是从 0 开始这点跟很多编程语言不一样。SELECT substring(PostgreSQL from 1 for 6); -- Postgr SELECT substr(PostgreSQL, 1, 6); -- Postgr替换用replace(str, from, to)这个跟 MySQL 一致没什么坑。但要注意它是全局替换不是只替换第一个匹配项。SELECT replace(hello world hello, hello, hi); -- 结果: hi world hi2.2 大小写转换与填充格式化场景常用比较常见的是upper、lower、initcap这三个函数。initcap会把每个单词的首字母大写在生成报告展示名称时很好用。SELECT upper(postgresql); -- POSTGRESQL SELECT lower(POSTGRESQL); -- postgresql SELECT initcap(hello world); -- Hello World还有lpad、rpad填充函数常用于把数字补成固定位数比如订单号补零。SELECT lpad(42, 5, 0); -- 00042 SELECT rpad(abc, 5, *); -- abc**这里有个细节如果原字符串长度已经超过目标长度不会截断而是直接返回原字符串。如果你需要固定长度并截断要先substr再lpad两个函数叠加使用。2.3 正则表达式与字符串分割PostgreSQL 的正则支持是它的一大优势。regexp_replace、regexp_matches、regexp_split_to_table这组函数在数据清洗时非常实用。-- 正则替换把连续的多个空格替换成单个 SELECT regexp_replace(a b c, \s, , g); -- 结果: a b c -- 正则分割把逗号分隔的字符串拆成多行 SELECT regexp_split_to_table(apple,banana,orange, ,); -- 结果三行: apple / banana / orange -- 提取匹配片段 SELECT regexp_matches(我的手机号是13812345678, [0-9]{11});三个点要提醒一下第一regexp_replace的第四个参数是 flags常用的g表示全局替换不加的话只替换第一个匹配项这个跟编程语言里的正则习惯一致。第二regexp_split_to_table返回的是集合不止能用一行而string_to_array返回的是数组类型。两者用途不同regexp_split_to_table适合直接用FROM引用的位置string_to_array需要配合unnest才能展开成行。第三正则里反斜杠的转义\s在字符串里需要用\s而不是\\s因为 PostgreSQL 字符串本身也支持反斜杠转义。很多人在这一步犯迷糊在普通 SQL 中建议直接写成\s。split_part也是个实用函数按分隔符分割后取指定位置的片段。SELECT split_part(2025-06-15, -, 2); -- 06它比regexp_split_to_array更轻量只要不是复杂正则优先用split_part。3. 数值与类型转换报表统计绕不开的基础工作数值计算和类型转换在统计类 SQL 里出现频率极高。PostgreSQL 的类型系统比 MySQL 严格好处是出错早、数据可信度高坏处是很多 MySQL 里能模糊处理的写法在这里直接报错。3.1 四舍五入与取整round、ceil、floor以及trunc这四个函数是处理数值精度的主力。SELECT round(42.438, 2); -- 42.44 SELECT round(42.4); -- 42注意返回类型 SELECT ceil(42.1); -- 43 SELECT floor(42.9); -- 42 SELECT trunc(42.438, 2); -- 42.43直接截断不舍入round和trunc的第二个参数表示保留的小数位。不传第二个参数时round返回 numeric 类型trunc对 numeric 和double precision的表现略有差异大表聚合时要留意精度问题。一个很容易忽略的点round对numeric类型是“四舍五入”但由于二进制浮点数的表示误差double precision类型可能出现意外的结果。做财务报表时建议把值先转成numeric再计算。SELECT round(2.675::double precision, 2); -- 意外可能得到 2.67 SELECT round(2.675::numeric, 2); -- 正常得到 2.683.2 类型转换三套写法都要会用PostgreSQL 类型转换有几种方式我按优先顺序排列推荐使用value::type格式可读性最好也可以使用CAST(value AS type)符合标准 SQL某些场景还可以用函数式转换比如to_char、to_numberSELECT 123::int; -- 123 SELECT CAST(123 AS int); -- 123 SELECT 12.3::numeric; -- 12.3 SELECT 2025-06-15::date; -- 2025-06-15 SELECT 42::text; -- 42类型转换最常见的坑是格式错误导致报错。比如把空字符串转 int 会报错把包含逗号的数字字符串转 numeric 也会报错。这种时候可以用CASE或regexp先做清洗或者用to_number函数指定格式SELECT to_number(1,234.56, 9,999.99); -- 1234.563.3 生成序号与随机数generate_series是 PostgreSQL 独有的利器可以生成连续数字序列或时间序列。造测试数据、补全日期维度、生成序列编号时极其好用。-- 生成 1 到 5 的连续数字 SELECT generate_series(1, 5); -- 结果: 1, 2, 3, 4, 5 -- 生成带步长的序列 SELECT generate_series(1, 10, 3); -- 结果: 1, 4, 7, 10 -- 生成时间序列比如每间隔 2 小时 SELECT generate_series(2025-01-01 00:00::timestamp, 2025-01-01 06:00::timestamp, 2 hours::interval);random()函数返回 0 到 1 之间的随机浮点数。要在区间内取整数需要配合floor和运算-- 生成 1 到 100 的随机整数 SELECT floor(random() * 100)::int 1;4. 日期时间函数数据分析里最常出问题的地方日期处理函数是每个做数据分析的人都绕不开的环节。PostgreSQL 的日期类型很丰富但函数写法和 MySQL 差异最大也是迁移时抱怨最多的地方。4.1 获取当前时间now()、current_timestamp、current_date、current_time这组函数要分清楚。SELECT now(); -- 2025-06-15 14:30:22.12308 SELECT current_timestamp; -- 同上now() 是它的别名 SELECT current_date; -- 2025-06-15 SELECT current_time; -- 14:30:22.12308 SELECT clock_timestamp(); -- 实时时间不同于 now() SELECT statement_timestamp(); -- 当前语句开始时间now()返回的是事务开始时间不是每条语句的实际执行时间。同一事务内多次调用now()返回一样的结果。需要获取每条语句真实执行时刻用clock_timestamp()。在长事务里做时间记录、日志对比时这个区别很关键。4.2 日期加减与间隔计算日期加减是使用频率最高的操作比如查询最近 7 天订单、上个月数据、本年度累计等。-- 日期加减 SELECT current_date 1; -- 明天 SELECT current_date - 7; -- 7天前 SELECT now() interval 3 hours; -- 3小时后 SELECT now() - interval 1 day; -- 1天前关于interval的写法PostgreSQL 支持比较宽松的格式。常见几种写法都可以正常工作SELECT now() interval 1 day; SELECT now() interval 1 day; -- 这种也合法 SELECT now() 1 day::interval; -- 推荐类型显式转换两个日期之间的间隔直接用减法得到的是天数。但两个 timestamp 相减得到的是 interval 类型要注意区分SELECT date 2025-06-15 - date 2025-06-01; -- 14天数整数 SELECT 2025-06-15 12:00::timestamp - 2025-06-15 10:00::timestamp; -- 结果: 02:00:00interval 类型要从 interval 里取天数或小时用extractSELECT extract(epoch FROM interval 2 hours); -- 7200秒数 SELECT extract(day FROM interval 3 days 4 hours); -- 3extract也可以从日期时间中提取年、月、日、星期等SELECT extract(year FROM now()); -- 2025 SELECT extract(month FROM now()); -- 6 SELECT extract(dow FROM now()); -- 星期几0是周日1是周一 SELECT extract(isodow FROM now()); -- 星期几1是周一7是周日dow和isodow的区别经常把人绕晕。简单说报表按周分组时优先用isodow它的星期顺序符合国际标准惯例周一到周日对应 1 到 7dow从周日算起对应 0 到 6。我的习惯是统一用isodow做按周统计。4.3 日期格式化函数to_char是日期转字符串的核心函数追溯整个 PostgreSQL 的日期格式化它提供的模板模式非常丰富。SELECT to_char(now(), YYYY-MM-DD HH24:MI:SS); -- 2025-06-15 14:30:22 SELECT to_char(now(), YYYY-MM-DD); -- 2025-06-15 SELECT to_char(now(), YYYY年MM月DD日); -- 2025年06月15日 SELECT to_char(now(), Dy, DD Mon YYYY); -- Sun, 15 Jun 2025对应的to_date和to_timestamp把字符串转成日期时间SELECT to_date(2025-06-15, YYYY-MM-DD); SELECT to_timestamp(2025-06-15 14:30:00, YYYY-MM-DD HH24:MI:SS);格式化模板里的坑主要集中在HH24才是 24 小时制HH是 12 小时制用错会导致早晚颠倒MM是月MI是分钟大小写不同含义完全不同。我见过有人把MI写成MM导致输出的分钟变成了月份这种错误光用肉眼看数据不容易发现做时间排序时才会暴露。4.4 时间与日期截断date_trunc函数可以把时间截断到指定精度做报表聚合时尤其好用。SELECT date_trunc(month, now()); -- 2025-06-01 00:00:00 SELECT date_trunc(day, now()); -- 2025-06-15 00:00:00 SELECT date_trunc(hour, now()); -- 2025-06-15 14:00:00 SELECT date_trunc(week, now()); -- 2025-06-09 00:00:00周一注意date_trunc的 week 模式默认从周一开始与isodow的语义一致。按周聚合时这个细节非常重要。age函数可以计算两个日期之间的年龄或时间差返回的是 interval 类型人类可读性很好。SELECT age(timestamp 2025-06-15, timestamp 2020-01-01); -- 结果: 5 years 5 mons 14 days SELECT age(timestamp 1988-03-22); -- 距今多少年月日5. JSON 与数组函数PostgreSQL 区别于传统关系库的特色功能一般数据库顶多支持 JSON 存储但 PostgreSQL 的 JSON 处理能力更像是一门独立的编程语言。配合数组函数可以做很多本来要放在应用层处理的逻辑。5.1 JSONB 基础操作jsonb类型与json类型最大的区别在于存储格式jsonb是解析后的二进制格式查询性能和索引支持都更好。正常情况下直接使用jsonb。-- JSON 取值用箭头操作符 SELECT {name: 张三, age: 30}::jsonb - name; -- 结果: 张三保留 JSON 引号 SELECT {name: 张三, age: 30}::jsonb - name; -- 结果: 张三纯文本 -- 嵌套取值用 # 和 # SELECT {info: {city: 上海}}::jsonb # {info, city}; -- 结果: 上海 SELECT {info: {city: 上海}}::jsonb # {info, city}; -- 结果: 上海四个操作符看起来差不多但用途分得很清楚-返回 JSON 类型-返回文本类型#和#用于嵌套路径。判断哪个该用就看后面的操作要再继续取下一级字段用-要跟文本比较或直接展示用-。判断键是否存在、是否存在某值用?和?|、?SELECT {a: 1, b: 2}::jsonb ? a; -- true SELECT {a: 1, b: 2}::jsonb ?| array[a, c]; -- true只要存在一个 SELECT {a: 1, b: 2}::jsonb ? array[a, b]; -- true必须全部存在JSON 数据聚合为一行用jsonb_agg。反过来行转 JSON 对象用jsonb_object_agg这两个函数在报表里非常实用-- 把多行数据聚合为 JSON 数组 SELECT jsonb_agg(name) FROM users; -- [[张三, 李四, 王五]] -- 把两列数据转成 JSON 对象 SELECT jsonb_object_agg(id, name) FROM users; -- {1: 张三, 2: 李四}我用 JSONB 函数做动态配置存储比较多。比如给每个订单存扩展字段查询时直接取出里面的某个值参与过滤和计算不需要改表结构灵活度很高。5.2 数组函数与unnestPostgreSQL 的数组类型本身就很好用。array_agg可以把一列的值聚合成数组unnest则把数组拆散成多行。-- 把成绩列聚合成数组 SELECT array_agg(score ORDER BY score) FROM student_scores; -- {60, 70, 80, 90} -- 把数组拆成多行 SELECT unnest(array[11, 22, 33]); -- 输出三行: 11 / 22 / 33数组与行的互相转换配合string_to_array、array_to_string可以处理很多 CSV 类的需求。SELECT array_to_string(array[a, b, c], ,); -- a,b,c SELECT string_to_array(a,b,c, ,); -- {a,b,c} -- 检查元素是否在数组里 SELECT b ANY(array[a, b, c]); -- true关于unnest的一个非常优秀的用法在 SQL 里做笛卡尔积式展开。比如某个订单包含多个商品商品数量存的是数组用unnest配合WITH ORDINALITY可以同时拿到下标和数据SELECT * FROM unnest(array[苹果, 香蕉, 橙子]) WITH ORDINALITY AS t(fruit, ord); -- 苹果 1 -- 香蕉 2 -- 橙子 3下标序号在还原原始数据顺序时非常重要比如页面提交的选项顺序、JSON 里保存的列表顺序。6. 聚合函数与窗口函数分析场景的必备武器统计报表离不开聚合函数而窗口函数则是 PostgreSQL 实现复杂分析场景的杀手锏。这一节把两组函数放在一起讲因为它们经常配合使用。6.1 常用聚合函数与 FILTER 子句聚合函数总体跟 MySQL 差异不大sum、avg、count、max、min是最基本的组合但count的几个写法需要特别注意SELECT count(*) FROM orders; -- 统计所有行 SELECT count(1) FROM orders; -- 同上性能差异可忽略 SELECT count(order_id) FROM orders; -- 统计非 NULL 的行 SELECT count(DISTINCT user_id) FROM orders; -- 去重统计FILTER子句是 PostgreSQL 特有的聚合扩展比CASE WHEN嵌套更简洁也不需要多处冗余-- 统计各分类的汇总值和满足条件的值 SELECT category, sum(amount) AS total_amount, sum(amount) FILTER (WHERE status paid) AS paid_amount, count(*) FILTER (WHERE status paid) AS paid_count FROM orders GROUP BY category;等价写法是用CASE WHEN但代码明显更长FILTER在可读性上优势很大。我个人的项目里凡是统计口径比较多的报表都优先用FILTER。array_agg作为聚合函数也值得提一下它可以把分组内的值拼成数组再配合array_to_string或string_agg输出。6.2 窗口函数分组内排序与累计计算窗口函数在报表场景中基本是顶梁柱。常用的四类排名类、累计类、偏移类、分组聚合类。排名类函数是最典型的窗口函数SELECT product_id, sales_amount, row_number() OVER (ORDER BY sales_amount DESC) AS row_num, rank() OVER (ORDER BY sales_amount DESC) AS rank, dense_rank() OVER (ORDER BY sales_amount DESC) AS dense_rank FROM product_sales;row_number和rank的区别在于处理并列row_number对并列数据强行编号不重复rank产生并列间隔如 1, 1, 3dense_rank产生并列连续如 1, 1, 2。取 Top N 时如果希望并列都进榜单用dense_rank如果严格取前 N 条记录用row_number。偏移类窗口函数比如取前一行或后一行的值SELECT day, sales_amount, lag(sales_amount, 1) OVER (ORDER BY day) AS prev_day_sales, lead(sales_amount, 1) OVER (ORDER BY day) AS next_day_sales FROM daily_sales;这种写法在做环比、同比分析时非常直接。比子查询关联的性能好很多代码也短。累计类窗口函数可以直接计算累计值比如每天累计销售额SELECT day, sales_amount, sum(sales_amount) OVER (ORDER BY day) AS cumulative_sales FROM daily_sales;窗口函数搭配PARTITION BY是分组统计的常态SELECT category, product_id, sales_amount, avg(sales_amount) OVER (PARTITION BY category) AS avg_in_category FROM product_sales;这是“每个分类的平均销售额”和“每条记录的销售额”同时展示一行数据包含当前记录和所在分组的上下文信息在报表导出时特别有用。7. 条件逻辑与空值处理让数据查询更健壮条件函数、空值函数是 SQL 的防御性编程利器。实际生产环境里脏数据、缺省值几乎是常态写 SQL 时不处理 NULL后面做应用层就会很痛苦。7.1 CASE WHEN 的灵活用法CASE WHEN是条件分支的标准写法能在 SELECT 列表、WHERE 子句、ORDER BY 里使用灵活度非常高。SELECT user_id, CASE WHEN order_count 10 THEN VIP WHEN order_count 1 THEN 普通用户 ELSE 新用户 END AS user_level FROM user_stats;注意CASE WHEN是表达式不是语句。可以在聚合函数里套用计算满足条件的值SELECT sum(CASE WHEN status paid THEN amount ELSE 0 END) AS paid_sum FROM orders;7.2 COALESCE 与 NULLIF 的坑COALESCE是最常用的空值处理函数返回参数列表中的第一个非 NULL 值SELECT coalesce(NULL, NULL, default); -- default SELECT coalesce(username, email, phone, 未知) FROM users; -- 依次取第一个非空值NULLIF的作用正好相反当两个参数相等时返回 NULLSELECT nullif(0, 0); -- NULL SELECT nullif(5, 0); -- 5NULLIF最常见的用途是防止除零错误SELECT total_amount / nullif(order_count, 0) AS avg_amount FROM user_stats;如果order_count是 0直接除会报错用NULLIF转成 NULL整体结果就是 NULL不会中断查询。还有一个GREATEST和LEAST在比较多个值时很有用SELECT greatest(1, 2, 3); -- 3 SELECT least(1, 2, 3); -- 1但注意它们的逻辑跟 MySQL 不完全一致遇到 NULL 会返回 NULL这点比 MySQL 更严格。7.3 DISTINCT ON 的独特价值DISTINCT ON是 PostgreSQL 特有的语法非常实用。它可以根据指定字段去重同时返回该分组内的任意一条记录配合ORDER BY可以精准控制取哪一条-- 取每个用户最新的一条订单 SELECT DISTINCT ON (user_id) user_id, order_id, order_time FROM orders ORDER BY user_id, order_time DESC;这个写法比窗口函数更简洁也不需要子查询。它内部处理逻辑是先按ORDER BY排序再对DISTINCT ON的字段去重保留每个组的第一条记录。注意ORDER BY里DISTINCT ON指定的字段必须排在最前面否则会报错。我经常用它来取最新价格、最新状态、每个用户最近一次登录时间等场景性能在索引合理的情况下相当不错。8. 常见问题与排查技巧实录这一节整理几个我在实际工作中踩过、也帮别人排查过的典型问题都是查文档不一定能快速找到答案的细节。8.1 PostgreSQL 与 MySQL 函数习惯冲突最常见的是字符串截取和拼接MySQL 的LEFT(str, n)、RIGHT(str, n)在 PostgreSQL 里也有但参数含义一致SUBSTRING则完全不同。PostgreSQL 支持标准 SQL 的substring(str from n for m)也支持substr(str, n, m)。从 MySQL 迁过来的人往往对substring的第一印象是“怎么这么别扭”所以我一般建议直接用substr。类型转换也是重灾区。MySQL 里123 0就能把字符串转成数字PostgreSQL 里必须用123::int或CAST写错一个字直接报错。这种严格反而让数据更可信因为类型错误在 SQL 执行阶段就暴露出来了。8.2 时区问题的排查实录曾经接了一个项目数据库存储的 timestamp 不带时区应用层传入的时间却是北京时间。取出来的数据做日期分组时凌晨的数据被归到前一天报表数据始终对不上。排查思路是先用current_time确认服务器时区然后检查连接字符串是否指定了timezone参数。后来统一约定数据库统一存 UTC所有取数操作在 SQL 里用AT TIME ZONE做转换。-- UTC 转北京时间 SELECT now() AT TIME ZONE UTC AT TIME ZONE Asia/Shanghai;8.3 隐式类型转换引发的索引失效还有一个比较隐蔽的性能问题在 timestamp 列上做字符串比较比如WHERE created_at 2025-06-15。表面看没问题但 PostgreSQL 的隐式类型转换可能导致索引无法使用。解决方法是显式转换-- 不推荐可能无法走索引 SELECT * FROM orders WHERE created_at 2025-06-15; -- 推荐让类型匹配 SELECT * FROM orders WHERE created_at 2025-06-15::date AND created_at (2025-06-15::date 1);这个例子也顺便说明了一个日常经验日期范围查询最好写成左闭右开区间既覆盖了整天数据又保证了索引最优使用。8.4 常见函数速查表整理一份我贴在工位上的速查表都是日常出现频率最高的功能推荐写法示例字符串拼接concat()concat(a, b)正则提取regexp_matches()regexp_matches(str, pattern)字符串分割取片段split_part()split_part(a-b-c, -, 2)防止除零NULLIFa / NULLIF(b, 0)空值兜底coalesce()coalesce(col, 默认值)日期格式化to_char()to_char(now(), YYYY-MM-DD)时间截断date_trunc()date_trunc(month, now())时间差秒extract(epoch from ...)extract(epoch FROM now() - start_time)JSON 取值-jsonb_col - field分组内取最新DISTINCT ONDISTINCT ON (user_id) ... ORDER BY user_id, time DESC累计求和sum() OVER ()sum(amount) OVER (ORDER BY day)9. 结尾的话从 MySQL 迁到 PostgreSQL刚开始会觉得函数写法别扭尤其是类型转换和字符串截取。但用久了就会发现PostgreSQL 的函数设计更贴近标准 SQL正则、JSON、窗口函数这块的能力是实打实的强。我在实际项目中体会最深的一点遇到不确定行为时直接用SELECT不带表跑一下函数几秒钟就能验证结果。PostgreSQL 允许这种纯表达式查询调试起来比 MySQL 舒服很多。最后分享一个我常用的组合技巧regexp_split_to_table加上unnest配合WITH ORDINALITY可以把很多复杂字符串解析问题拆成简单的行级操作。把数据展开成行之后再配合窗口函数和FILTER做统计分析整个处理流程会非常顺滑。这篇文章列出的函数基本覆盖了日常开发中九成以上的需求。实际操作中我对每类函数都建议在自己环境的数据库里敲一遍观察返回类型和结果。函数返回值的数据类型决定了下一步能调哪些函数这是文档里不太会强调、但实际写 SQL 时非常关键的一点。