Postgres fast wrapping arithmetic operations without overflow on bigint

Question

Is there any way to do wrapping operations (addition, multiplication, SUM) on bigint columns?

By this I mean the standard method of wrapping addition in other programming languages on integer types of a fixed bit-length, e.g. in C++

#include <cstdint>
#include <iostream>

int main() {
    int64_t t = 9223372036854775807;
    t += 1;
    std::cout << t << '\n'; // prints -9223372036854775808
}

In PostgreSQL, I get an overflow:

SELECT 9223372036854775807 + 1;

ERROR:  bigint out of range

For aggregation functions in Postgres, it seems the standard behavior is to cast to variable-length numbers, and then calculate the sum, e.g.

SELECT pg_typeof(SUM(id)) FROM (VALUES(9223372036854775807), (9223372036854775807)) AS t(id);

 pg_typeof 
-----------
 numeric
(1 row)

This also works for regular numbers:

SELECT 9223372036854775807::numeric + 1;

      ?column?       
---------------------
 9223372036854775808
(1 row)

I could of course do a modulo operation here and get the result I want, but going via variable-length numbers will be a lot slower than pure 64-bit operations.

Is there any way to force standard wrapping 64-bit integer operations?

There is nothing standard about wrapping 64-bit integers on overflow: the behavior varies across languages. C++ essentially performs modular ariithmetic; therefore, overflow doesn't occur. PostgreSQL treats BIGINT as an integral value within a range: operations that would result in values outside of that range cause an exception to be raised. When dealing with quantiities, raising an overflow exceptiion is preferable to returning erroneous results. There is no way to force PostgreSQL to wrap integer operations. — JohnH
– JohnH, Commented Feb 5 at 5:16
AFAIK using wrapping is how most CPUs implement arithmetic operations — Mark
– Mark, Commented Feb 5 at 8:21
CPU arithmetic operations affect both register values and status flags, such as overflow. At the CPU level, register values have limited semantic meaning and constraints. PostgreSQL's BIGINT is more than just a collection of 64 bits: it is a signed integral quantity. BIGINT values don't have associated statuses so overflows are signaled by raising an exception. This is analogous to a CPU raising a segmentation fault when an address references a location outside of the permitted range. — JohnH
– JohnH, Commented Feb 5 at 16:00

Laurenz Albe · Accepted Answer · 2025-02-05 08:18:34Z

1

I don't think that there is a straightforward way to have aggregate functions that aggregate bigints to a bigint and wrap around numbers.

But PostgreSQL is very extensible: you could write your own aggregate function to do that. If you want good performance and make use of C's wraparound, you should write the state transition function in C.

answered Feb 5 at 8:18

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres fast wrapping arithmetic operations without overflow on bigint

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related