Thread: Support for unsigned integer types
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? Thanks!
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 thenthere'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 Sat, Dec 7, 2024 at 8:24 AM Tom Lane <tgl@sss.pgh.pa.us> 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. > > regards, tom lane
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.