I have a postgresql database and a table like this:

Column         |              Type              |
--------------- -------------------------------- --
id             | bigint                         |
amount         | numeric(8,2)                   | 

More precisely, I have :

id   | amount  |
----- --------- -
 509 | -100.00 |
 517 |  -10.50 |
 518 |   -7.40 |

I want to change the type of amount to bigint. Before doing that, I would like to multiply each entry by 100.

I tried to do this: update entrees set amount = amount * 100;

But I got these errors (I get them in French, translation is mine:)

ERROR:  numerical field exceeds limits 
DETAIL : A field with precision 6 and scale 2 must be rounded to a value that is absolutely inferior to 10^4.

My guess is that I need to change the type numeric(6,2) to something else before the multiplication.

Is that true? How do I multiply all entries into this field by 100?

CodePudding user response：

I would do like this:

alter table table_name alter column amount type bigint;

UPDATE table_name SET amount = (amount * 100);

alter table table_name alter column amount type numeric(8,2);

CodePudding user response：

Safest way would be to:

1. add another column of type bigint,

2. use it to store the multiplied values

3. check if new values are what you need;

4. delete existing amount column

5. rename new column to amount

   ALTER TABLE entrees ADD COLUMN amount_int BIGINT;

   UPDATE entrees SET amount_int=amount*100;

   ALTER TABLE entrees DROP COLUMN amount;

   ALTER TABLE entrees RENAME COLUMN amount_int TO amount;

CodePudding user response：

You can do that in a single ALTER table:

alter table the_table
   alter amount type bigint using (amount * 100)::bigint;