Sample Header Ad - 728x90

Compare signed integers as if they were unsigned

2 votes
2 answers
375 views
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