Introduction
In certain situations, it can be beneficial to ignore integer overflow errors. Consider, for instance, an integer table column that typically accommodates small values. On rare occasions, a significantly larger value might be inserted. Attempting to aggregate these values could lead to an integer overflow error, causing the entire aggregate query to fail, which is far from ideal.
Handling large values on the client or worker side, where the data is inserted, is one option. However, if you prefer to manage this issue at the database level, using saturation arithmetic could be a practical solution. The main idea of saturation arithmetic: if the the result of an operation exceeds the maximum allowed value or falls below the minimum allowed value, it is set to the respective maximum or minimum limit.
Some programming languages include saturation arithmetic in their standard libraries. For instance, both Rust and C++ libraries offer dedicated functions for this purpose.
pg-saturated_int Extension
At Adjust, we’ve developed the pg-saturated_int PostgreSQL extension, which implements functions utilizing saturation arithmetic.
pg-saturated_int offers support for the following operators and functions:
- comparison operators:
<,<=,<>,=,>,>= - arithmetic operators:
*,/,%,+,- - aggregate function:
sum
Additionally pg-saturated_int supports btree and hash indexes.
To install the extension, you need to clone the repository, build the binaries and then create the extension in your database:
git@github.com:adjust/pg-saturated_int.git
cd pg-saturated_int
make install
psql -c "CREATE EXTENSION saturated_int"
Examples
You can handle the saturated_int type in the same manner as you would with int.
Below are examples demonstrating casting to saturated_int:
select 999999999999999::saturated_int;
saturated_int
---------------
2147483647
select 2147483648::saturated_int;
saturated_int
---------------
2147483647
select (-2147483649)::saturated_int;
saturated_int
---------------
-2147483648
Below are examples demonstrating the use of operators with saturated_int:
select 999999999999999::saturated_int > 2147483648::saturated_int;
?column?
----------
f
select 999999999999999::saturated_int = 2147483648::saturated_int;
?column?
----------
t
select 999999999999999::saturated_int * 2147483648::saturated_int;
?column?
------------
2147483647
select (-999999999999999)::saturated_int * 2147483648::saturated_int;
?column?
-------------
-2147483648
select 2147483648::saturated_int / (-1)::saturated_int;
?column?
-------------
-2147483647
Please note that it is necessary to explicitly cast literals of bigint and
int types to saturated_int. At present, implicit casting isn’t supported
intentionally. It remains possible to insert values of bigint and int types
into a table with a column of saturated_int without requiring explicit casting.
Below an example demonstrating that:
CREATE TABLE saturated_test (id saturated_int);
INSERT INTO saturated_test VALUES (999999999999999), (1::int), (2147483647);
You can check values of the table and the sum aggregate function now:
SELECT * FROM saturated_test;
id
────────────
2147483647
1
2147483647
SELECT sum(id) FROM saturated_test;
sum
────────────
2147483647