I'm trying to increase the precision of an already existing table so that new data that is more precise, doesn't get rounded off. I still want to keep the old data around.
I have a table that has a column as follows:
rate NUMERIC(10,4) not null
I'm creating a table that has the column as follows:
rate NUMERIC(10,6) not null
I'm trying to insert the data from the old table into the new column by doing this:
INSERT INTO new_table (SELECT * FROM old_table)
I get an error as follows:
[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] -----------------------------------------------
How can I copy data into the new table from the old table?
Any help is appreciated!
Things I've tried:
I tried CASTING as a NUMERIC(10,6) but it didn't work. Also tried multiplying by 1.000000 and doing a ROUND, but no luck there either.
@Marth to the rescue!
Did you mean to create a NUMERIC(12, 6)? A NUMERIC(10, 6) can only go up to 10^4-1. Your NUMERIC(10, 4) data probably has values greater than that (since it can go up to 10^6-1).
Changing the table definition to NUMERIC(12,6) worked! I could copy stuff in.