Support for unsigned integer types

Jack Bay <jack.victor.bay@gmail.com> writes:

Would it be possible to add support for unsigned 64-bit and unsigned
32-bit integers to postgresql?

This has been discussed before, and we've concluded that the impact
on the numeric promotion hierarchy (that is, implicit-cast rules
among the integer types) would probably be catastrophic, leading
to problems like ambiguous-operator errors in many cases that were
fine before. Quick, is "42 + 1" an int32 or uint32 operation?

That could be avoided perhaps by measures like not having any
implicit casts between the int and uint hierarchies, but then
there'd be a corresponding loss of usability for the uint types.

Plus, the sheer magnitude of effort needed to build out a reasonable
set of support (functions, operators, opclasses) for uint types seems
daunting.

On the flip side, it'd be great to be able to use uint32 instead
of bigint for the SQL representation of types like BlockNumber.
But we couldn't roll in such a change transparently unless we make
int-vs-uint casting fairly transparent, which seems problematic
as per above.

Perhaps a sufficiently determined and creative person could put
together a patch that'd be accepted, but it'd be a lot of work
for uncertain reward. I'm not aware that anyone is working on
such a thing at present.

regards, tom lane

That could be avoided perhaps by measures like not having any implicit casts between the int and uint hierarchies, but then there'd be a corresponding loss of usability for the uint types.

In my opinion no explicit cast between unsigned and signed integer is
very desirable behavior.

Quick, is "42 + 1" an int32 or uint32 operation?

Or is it int64 or uint64, when should it overflow? Or is it a floating
point operation? Or are there mismatched numeric types, because one is
a float and the other is an integer? In this circumstance, most likely
42 would be a variable with a known type so you could deduce the
intended type of 1 which presumably is a constant.

This gets into questions around the SQL type system I am unfamiliar with.

ambiguous-operator errors in many cases that were fine before.

Adopt a convention that integers are signed unless explicitly made unsigned?

To be explicit, you could write 42u64 + 1u64 or 42u32 + 1u32...

On 06.12.24 19:45, Jack Bay wrote:

Unsigned integers are a widely used type and can be important for
compact and well-aligned data structures, but are not currently
available in PostgreSQL.

In particular unsigned 64-bit integers and unsigned 32-bit integers
are desirable as identifiers. They unambiguously correspond to
specific hexadecimal or other human-readable encoded representations.
Although signed integers can be used for this purpose, there is the
potential for human error in confusing a positive value for a negative
value, corner cases around maximum and minimum values (which are
statistically certain to be encountered when random bits are used for
the integer), the potential for human error in interconverting hex and
other encoded representations, text representation nonuniformity (the
need for a space for the minus sign), and a variety of associated
nuisances.

Would it be possible to add support for unsigned 64-bit and unsigned
32-bit integers to postgresql?

Here is an extension that implements this: https://github.com/petere/pguint

You can use this for production use and perhaps also as the basis for
experimentation about different behaviors and trade-off that have been
mentioned.

On Sat, Dec 7, 2024 at 11:24:37AM -0500, Tom Lane wrote:

Jack Bay <jack.victor.bay@gmail.com> writes:

Would it be possible to add support for unsigned 64-bit and unsigned
32-bit integers to postgresql?

This has been discussed before, and we've concluded that the impact
on the numeric promotion hierarchy (that is, implicit-cast rules
among the integer types) would probably be catastrophic, leading
to problems like ambiguous-operator errors in many cases that were
fine before. Quick, is "42 + 1" an int32 or uint32 operation?

That could be avoided perhaps by measures like not having any
implicit casts between the int and uint hierarchies, but then
there'd be a corresponding loss of usability for the uint types.

Plus, the sheer magnitude of effort needed to build out a reasonable
set of support (functions, operators, opclasses) for uint types seems
daunting.

On the flip side, it'd be great to be able to use uint32 instead
of bigint for the SQL representation of types like BlockNumber.
But we couldn't roll in such a change transparently unless we make
int-vs-uint casting fairly transparent, which seems problematic
as per above.

Perhaps a sufficiently determined and creative person could put
together a patch that'd be accepted, but it'd be a lot of work
for uncertain reward. I'm not aware that anyone is working on
such a thing at present.

We do have the 'oid' data type, which is an unsigned 4-byte integer, but
it lacks the casts and operator support mentioned above:

SELECT 42 + 1;
?column?
----------
43

SELECT 42::oid + 1;
ERROR: operator does not exist: oid + integer
LINE 1: SELECT 42::oid + 1;
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

SELECT 42::oid + 1::oid;
ERROR: operator does not exist: oid + oid
LINE 1: SELECT 42::oid + 1::oid;
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Do not let urgent matters crowd out time for investment in the future.