PostgreSQL数字转千分位逗号分隔出现异常的问题求助

问题场景 #

原始表数据：

|     size      |
|---------------|
| 2880129015916 |
|          2560 |
|             0 |
|      91570752 |
|          8192 |
|      21037156 |

需求：将size列数值转换为千分位逗号分隔格式，例如2880129015916需显示为2,880,129,015,916。

执行的SQL语句：

SELECT v99.size,
    rtrim(ltrim(v99.size::money::character varying::text, '$'::text), '.00'::text) AS x
FROM v99;

异常表现 #

• 2560被格式化为2,56
• 0被格式化为空字符串
• 其余数值格式正常

异常原因 #

1. money类型的区域格式限制：PostgreSQL的money类型依赖数据库的lc_monetary区域设置，若你的区域配置中，货币格式会省略小数部分末尾的零，那么2560转为money后会变成$2,56（而非预期的$2,560.00），后续的裁剪操作就会得到错误结果。
2. 0的裁剪逻辑问题：0转为money是$0.00，经过ltrim去掉$后得到0.00，再用rtrim('.00')会把所有匹配的字符从末尾移除，最终将0.00裁剪成空字符串。

正确解决方案 #

直接使用to_char函数进行格式化，无需依赖money类型的转换，更可靠灵活：

SELECT size, to_char(size, 'FM999,999,999,999,999') AS x
FROM v99;

• FM前缀用于消除结果中的前导空格
• 模式中的9代表任意数字位，逗号作为千分分隔符，可根据实际数值的最大长度调整模式中的位数数量。

执行后结果完全符合需求：

• 2880129015916 → 2,880,129,015,916
• 2560 → 2,560
• 0 → 0
• 其他数值均能正确显示千分位分隔。

内容的提问来源于stack exchange，提问作者Ed Kan