Oracle NUMBER类型是否自动去除末尾全零小数位？迁移Postgres遇格式问题

问题背景 #

• 数据库迁移场景：从Oracle迁移至Postgres，AMOUNT列定义从NUMBER(18,2)同步为NUMERIC(18,2)
• 异常表现：
  • 写入3000.00时，Oracle存储为3000，最终XML输出为3000（符合下游第三方应用预期）；Postgres存储为3000.00，XML输出为3000.00（导致下游无法处理）
  • 写入3000.01时，Oracle和Postgres均正常保留两位小数，输出符合预期

结论 #

这是Oracle NUMBER 类型的预期固有行为。Oracle的NUMBER(p,s)类型在存储时会自动优化数值表示：

• 当小数部分全为零时，会自动截断末尾的.00，以整数形式存储和返回；
• 当小数部分存在非零值时，严格遵循定义的小数位精度保留数值。

虽然Oracle官方文档未单独针对该场景做高亮说明，但这是其数值类型存储机制的一部分，属于默认行为。

适配建议（Postgres侧对齐Oracle输出） #

如果需要让Postgres的输出和Oracle一致，可通过以下方式处理：

1. Java应用层处理：读取数值后判断小数部分是否为.00，是则输出整数格式，否则保留两位小数；
2. Postgres查询层处理：使用字符串截断函数去除末尾的.00，例如：

   SELECT TRIM(trailing '.00' FROM AMOUNT::TEXT) AS FORMATTED_AMOUNT FROM your_table;
   

3. 调整列类型（谨慎使用）：将Postgres的列类型改为NUMERIC(18)，但会丢失对两位小数的约束，需评估业务影响。

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