Postgres 11.7数值格式化：保留两位小数起、删多余尾零、加千分位分隔符

需求说明 #

• 为数值添加千分位分隔符
• 始终最少保留两位小数，超出两位小数后的末尾零可删除

问题复现 #

原有使用to_char加FM修饰符的写法，在小数部分全为0时会出现不符合预期的结果：

-- 示例1：输出符合预期
SELECT to_char(54354.0010, 'FM99 999 999 990.999999'); --> 返回 54 354.001

-- 示例2：输出不符合预期，小数点及后续零全被移除
SELECT to_char(54354.0000, 'FM99 999 999 990.999999'); --> 返回 54 354.
-- 预期输出：54 354.00

问题原因：FM修饰符的作用是移除所有前导空格、末尾零和尾部小数点，因此当小数部分全为0时，会直接删除小数点及后续内容，无法满足最少保留两位小数的要求。

解决方案 #

可以通过子查询做基础格式化后再二次校验补全，写法如下，兼容性适配PostgreSQL 11.7版本：

SELECT 
  CASE
    -- 无小数点时补充.00
    WHEN strpos(formatted_val, '.') = 0 THEN formatted_val || '.00'
    -- 小数点后仅1位时补1个零
    WHEN length(split_part(formatted_val, '.', 2)) = 1 THEN formatted_val || '0'
    -- 其余情况直接返回
    ELSE formatted_val
  END AS final_formatted_val
FROM (
  -- 第一步先做基础格式化，可根据实际业务调整整数位、小数位的最大长度
  SELECT to_char(your_numeric_column, 'FM99 999 999 990.999999') AS formatted_val
  FROM your_table_name
) t

测试验证 #

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