Postgres: data types - How to store uint64 in postresql

Question

Bigint in postgresql is 8 byte integer. which is has half the range as uint64 (as one bit is used to sign the integer)

I need to do a lot of aggregation on the column and I am under the impression that aggregation on NUMERIC type is slow in comparison to integer types.

How should I optimize my storage in this case?

Answer 1

Unless you have a concrete reason, just use NUMERIC. It is slower, quite a lot slower, but that might not matter as much as you think.

You don't really have any alternative, as PostgreSQL doesn't support unsigned 64-bit integers at the SQL level. You could add a new datatype as an extension module, but it'd be a lot of work.

You could shove the unsigned 64-bit int bitwise into a 64-bit signed int, so values above maxuint64/2 are negative. But that'll be totally broken for aggregation, and would generally be horribly ugly.

Answer 2

There is also an extension to provide an additional uint64 datatype in postgresql. See Github

It is by Peter Eisentraut

Answer 3

sum() will return numeric if the input is bigint so it will not overflow

select sum(a)
from (values (9223372036854775807::bigint), (9223372036854775807)) s(a)
;
         sum          
----------------------
 18446744073709551614

http://www.postgresql.org/docs/current/static/functions-aggregate.html