I have two queries. I expect both insert same value: 429496729600, but one of them fail because of error:
db=> update order_detail set amount = 400*1024*1024*1024 where id = 11;
ERROR: integer out of range
db=> update order_detail set amount = 429496729600 where id = 11;
UPDATE 1
Why the error occur for first query?
UPD
Forget to specify that type of amount is bigint and the
400*1024*1024*1024 == 429496729600
To force the multiplication to output a bigint instead of an int, you can cast 1 to a bigint and multiply
select cast(1 as bigint)*400*1024*1024*1024;
?column?
--------------
429496729600
CAST((400*1024*1024*1024) AS bigint)?
int maximum value of 231-1, the first Update value greater than it so caulse the erorr.
INT-2147483648 to +2147483647
You can try to let amount column to BIGINT Type
BIGINT-9223372036854775808 to 9223372036854775807
ALTER TABLE order_detail ALTER COLUMN amount TYPE BIGINT;
EDIT
we can use pg_typeof to check it out.
Query #1
postgresql will let 429496729600 be BIGINT because of the value greater than int range.
SELECT pg_typeof(429496729600 );
| pg_typeof |
| --------- |
| bigint |
Query #2
When you do multiplication in number that will translate to int.
SELECT pg_typeof( 1*15*1 );
| pg_typeof |
| --------- |
| integer |
Query
You can use 400*1024*1024*1024:: BIGINT let int convert to bigint.
SELECT 400*1024*1024*1024 :: BIGINT;
| ?column? |
| ------------ |
| 429496729600 |
