I am using PostgreSQL to store 64 bits IDs. The IDs are unsigned and make full use of all their bits (including the first one). To store said IDs, I am currently converting them to signed integers before inserting them as
bigint
(because Pg doesn't support unsigned integers).
This does work, and I am able to convert back the IDs to unsigned integers after querying them, but I ran into a problem: if I try to compare IDs in an SQL request, it will fail because some of them are negative.
For example, if I try to store 18446744069414584320
and 25
inside my DB, those IDs will be stored as -4294967296
and 25
respectively. Hence for Pg, the largest ID would be 25
(which is problematic in my case).
Is there a way to treat signed integers as unsigned when using a comparison operator in PostgreSQL ?
Edit: Storing the IDs using the numeric
data type would not be practical in my case not only because of the increased memory consumption but also because of the way IDs are processed by the applications connected to the database.
Asked by Sadiinso
(123 rep)
Feb 18, 2022, 08:40 AM
Last activity: Feb 22, 2022, 08:38 AM
Last activity: Feb 22, 2022, 08:38 AM