CODEWITHSUNDEEP
sqlpostgresql
northsideknight
northsideknight

Reputation: 1557

Alter Numeric Column to Lower Precision

I have a numeric column in a table in my postgres database schema, it has precision of n.

I need to update this column to be a lower precision, call it m.

So m < n.

When I run the command:

ALTER TABLE my_table
ALTER COLUMN my_column
TYPE numeric(m, 2)

I get the following error: numeric field overflow

I understand this is because I have values stored in the table that have precision greater than m (what I want the new precision to be). Is there any way I can tell postgres to change the precision and ignore the lost precision? ex.

if n = 20 and m = 15 and I have a value in the my_column that is 99999999999999999, this precision is 17 if I am not mistaken, can I tell postgres to ignore the lost precision and save the new value as 999999999999999 when I alter the table/column?

Upvotes: 1

Views: 1445

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You can use using to provide an expression for the conversion:

alter table t alter column col
    type numeric(2) using (case when col >= 100 then 99 else col end);

Here is a db<>fiddle.

Upvotes: 1

Related Questions