AWS Redshift中DECIMAL(10,4)转DECIMAL(10,6)插入报错求解决

需要提升现有表的精度，避免新的高精度数据被截断，同时保留旧数据：

• 旧表字段：rate NUMERIC(10,4) not null
• 新表对应字段：rate NUMERIC(10,6) not null

执行插入语句时报错：

INSERT INTO new_table (SELECT * FROM old_table)

错误信息：

[2023-07-18 10:42:16] [XX000] ERROR: Numeric data overflow (result precision)
[2023-07-18 10:42:16] Detail:
[2023-07-18 10:42:16] -----------------------------------------------
[2023-07-18 10:42:16] error:  Numeric data overflow (result precision)
[2023-07-18 10:42:16] code:      1058
[2023-07-18 10:42:16] context:   64 bit overflow
[2023-07-18 10:42:16] query:     61934434
[2023-07-18 10:42:16] location:  numeric_bound.cpp:112
[2023-07-18 10:42:16] process:   query0_123_61934434 [pid=13888]
[2023-07-18 10:42:16] -----------------------------------------------

已尝试CAST为NUMERIC(10,6)、乘以1.000000、ROUND操作，均未解决问题，求旧表数据复制到新表的可行方案。

错误原因分析 #

NUMERIC(p,s)中，p是总精度（整数+小数的总位数），s是小数位数：

• 旧表NUMERIC(10,4)：最多支持6位整数+4位小数（10-4=6）
• 新表NUMERIC(10,6)：最多支持4位整数+6位小数（10-6=4）

如果旧表中存在整数部分超过4位的数据（比如99999.9999），直接转换会触发溢出错误。

步骤1：排查数据范围 #

先确认旧表中是否存在超出新表范围的数据：

-- 查看整数部分最大值
SELECT MAX(FLOOR(rate)) FROM old_table;
-- 查看整数部分最小值（负数情况）
SELECT MIN(CEIL(rate)) FROM old_table;
-- 直接筛选超出新表范围的数据
SELECT * FROM old_table WHERE rate >= 10000 OR rate <= -10000;

步骤2：优先方案——调整新表精度（推荐） #

如果要完整保留旧表数据，需修改新表的总精度，确保整数部分位数兼容旧表：
旧表最多有6位整数，新表需要6位整数+6位小数，总精度设为NUMERIC(12,6)：

-- 修改新表字段类型（若新表已创建）
ALTER TABLE new_table ALTER COLUMN rate TYPE NUMERIC(12,6);
-- 执行插入
INSERT INTO new_table SELECT * FROM old_table;

步骤3：迫不得已的兼容方案（不推荐，会丢失数据） #

若必须保持新表为NUMERIC(10,6)，需处理超出范围的数据，例如截断到新表允许的最大值/最小值：

INSERT INTO new_table (rate)
SELECT 
  CASE 
    WHEN rate >= 10000 THEN 9999.999999
    WHEN rate <= -10000 THEN -9999.999999
    ELSE CAST(rate AS NUMERIC(10,6))
  END
FROM old_table;

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