Avoid Unexpected Integer Out of Range Error when Multiplying Integers

According to the YugabyteDB documentation, the largest value that can be stored in an integer is 2,147,483,647.

This is because for non-qualified integers, YSQL uses the type INT4, i.e. 32-bit integers.

Without that knowledge you might be a little perplexed when you’re greeted with an “integer out of range” error when trying to multiply several relatively small integers.

Example:

				
					yugabyte=# CREATE TABLE test (c1 INT, c2 INT, c3 INT, c4 INT);
CREATE TABLE

yugabyte=# INSERT INTO test VALUES (256, 256, 256, 256);
INSERT 0 1

yugabyte=# SELECT c1*c2*c3*c4 product FROM test;
ERROR:  integer out of range

To get around this, simply convert one of the factors in an INT8.

				
					yugabyte=# SELECT c1::INT8*c2*c3*c4 product FROM test;
  product
------------
 4294967296
(1 row)

Have Fun!