How to prepare a 2^64 (unsigned bigint) for a 2^63-1 (signed int) column using PHP?

I need to store the number 17557386533328272186 in a signed bigint column.

Postres does not support unsigned fields because the SQL specification does not define unsigned fields. As such, the largest integer that may be stored in a Postgres bigint column is 9223372036854775807.

If I want to store the value from this perceptual hashing library, I need to be able to store an unsigned int that will regularly be in the 2^64 range.

My numbers are being handled by GMP, so a GMP-based solution is a good solution.

What I ended up with is this.

signed to unsigned (sql to working):
gmp_strval(gmp_add(gmp_init($theirPhash, 10), gmp_pow(2, 63)), 16))

unsigned to signed (for sql):
gmp_strval(gmp_sub(gmp_init("0x{$fileHash->toHex()}", 16), gmp_pow(2, 63)), 10)

Solution

Subtract 2^63 before you insert into the DB, then add it again when you retrieve it:

$max = '9223372036854775807'; // 2^63
$realValue = '17557386533328272186';
$dbValue = gmp_sub($realValue, $max);
var_dump($dbValue);
// INSERT (?), $dbvalue

// Get the value back again
// $dbValue = SELECT dbValue
$realValue = gmp_add($dbValue, $max);
var_dump($realValue);