Recently, I had to create a PostgreSQL equivalent of the Java hashCode() function. In order to do this, I needed a way to make sure that numbers that exceed the INTEGER representation (ranging from -2^31 to 2^31-1) will be wrapped. In JavaScript, this is as simple as using the logical shift left operator on the number. On the other hand, I was unable to find a simple way to do this in PostgreSQL. Therefore, I started using JavaScript to figure out a way to create a function which would allow me to simulate what is natively available in JavaScript. I came up with the following JavaScript function:

function wrapNumber(num, bits, unsigned) {
  var most = Math.pow(2, bits), ret = num + (unsigned ? 0 : most / 2);
  return (ret<0 ? Math.ceil(-ret/most) : -Math.floor(ret/most)) * most + num;
}

After testing the above function, I created the following PostgreSQL function:

CREATE OR REPLACE FUNCTION wrap_signed_bigint(
  IN in_number BIGINT,
  IN in_bits SMALLINT
)
RETURNS BIGINT AS $$
DECLARE
  most BIGINT;
  ret BIGINT;
BEGIN
  most := 2 ^ in_bits;
  ret := in_number + most / 2;
  IF ret >= 0 OR (ret / most) * most = ret THEN
    ret := -ret / most;
  ELSE
    ret := -ret / most + 1;
  END IF;
  RETURN ret * most + in_number;
END;
$$ LANGUAGE plpgsql;

Now if a number outside of the 32-bit range is passed in, a corresponding number that is in that range will be returned:

SELECT num,
  msg,
  wrap_signed_bigint(CAST(num AS BIGINT), CAST(32 AS SMALLINT))
FROM (
  SELECT 'lowest negative number' AS msg, 2^31 AS num
  UNION SELECT 'back to zero' AS msg, 2^32 AS num
  UNION SELECT 'highest positive number' AS msg, -2^31 - 1 AS num
) t

By the way, if you want a signed version of the wrap function, you may use this code:

CREATE OR REPLACE FUNCTION wrap_unsigned_bigint(
  IN in_number BIGINT,
  IN in_bits SMALLINT
)
RETURNS BIGINT AS $$
DECLARE
  most BIGINT;
  ret BIGINT;
BEGIN
  most := 2 ^ in_bits;
  ret := in_number;
  IF ret >= 0 OR (ret / most) * most = ret THEN
    ret := -ret / most;
  ELSE
    ret := -ret / most + 1;
  END IF;
  RETURN ret * most + in_number;
END;
$$ LANGUAGE plpgsql;

Leave a Reply

Your email address will not be published. Required fields are marked *