uint64 values with high bit set are not supported about pq

[paddycarver]

My solution to this so far is to use two functions to hex encode the data in a varchar prior to inserting it and decode it when retrieving it. Which really rather defeats the purpose of packing the information in a uint64 in the first place, doesn't it? Any better solution I'm missing?

[fdr]

Unfortunately uint64s are not supported by Postgres. Actually, no unsigned quantities are, unless you count the system type oid which is a uint32 if memory serves...I don't recommend relying on that, though. You can use a numeric, but that'll be slower. Another option is bytea, but I think the underlying varlena will introduce a lot of overhead also.

The nuclear option is to write an extension that supports unsigned integers for Postgres in C, and install it...then perhaps teach pq to understand how to deal with encodings for user defined types, which has issues similar to #49. I'm surprised such a 'unsigned integer extension' doesn't show up quickly via web search. Someone last had a go in 2008 it seems.

A workaround would be to use the 'uuid' type, which is double-wide at 128, but you can choose to just use the low bits or something. In spite of their rendition on the wire, they are stored as fixed-precision 128-bit quantities. This is still slower than a real 64-bit integer, but faster than numeric in all likelihood.

[fdr]

(Seeing as how this is not a pq-solvable issue, putting aside user-defined-data-types and encodings, closing)

[paddycarver]

Awesome, thanks for the ideas. Appreciate it.