Recently, I experienced that PostgreSQL is wrong while divided big numbers. I was converting uuid to base57, to do it I need to convert uuid to to int then divided by 57 until it gave me zero. But uuid converted to integer is huge and PostgreSQL doesn’t handle well division with huge number, it make some approximations.
Here is an example :
# select 5669625960228265471347098101845929348/57;
?column?
-------------------------------------
99467122109267815286791194769226831
(1 row)Actually, the result is 99467122109267815286791194769226830
To avoid the approximation, I create the following function :
CREATE OR REPLACE FUNCTION safe_divmod(n numeric, d numeric)
RETURNS TABLE(quotient numeric, remainder numeric) AS $$
DECLARE
q numeric;
r numeric;
BEGIN
q := trunc(n/d); -- instead of floor
r := n - (q * d); -- Compute mod
-- Make sure that 0 ≤ r < d
IF r < 0 THEN
r := r + d;
q := q - 1;
END IF;
RETURN QUERY SELECT q, r;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;And here it is :
# SELECT safe_divmod(5669625960228265471347098101845929348,57);
safe_divmod
------------------------------------------
(99467122109267815286791194769226830,38)
(1 row)