CODEWITHSUNDEEP
postgresql
Paul A Jungwirth
Paul A Jungwirth

Reputation: 24541

Convert long Postgres bytea to decimal

I have a Postgres bytea value and I'd like to store it as a decimal type (or alternately as a string with decimal digits). It is too long to store as a bigint, but the docs say decimal values can have up to 131072 digits, and that is big enough. This is about the length I need to handle (150-300 digits in hex):

c30d040703020095dbb3d3746d096dd23b01c59bcbc7a4320f571511f61f3ea3def0e55404204a274500224927421bd5a8344a56316b909ef3af276b585622f1c9b7ca13563ee6fe88f4ddbe

The trouble is none of the similar questions handle numbers that large. Anyone have a solution?

Upvotes: 3

Views: 1997

Answers (1)

redneb
redneb

Reputation: 23840

Here's my take:

CREATE OR REPLACE FUNCTION parse_hex(s text) RETURNS numeric AS $$
DECLARE
    len integer;
    result  bigint;
BEGIN
    SELECT length(s) INTO len;
    IF len <= 15 THEN
        EXECUTE 'SELECT x''' || s || '''::bigint' INTO result;
        RETURN result::numeric;
    ELSE
        RETURN parse_hex(left(s, (len+1)/2)) * (16::numeric)^(len/2) +
               parse_hex(right(s, len/2));
        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

It uses binary splitting, i.e. it splits the input into 2 halves and converts them by recursively invoking itself and then combines the 2 parts.

Edit: I just run a simple benchmark to compare the above function with the one linked by Abelisto:

$ EXPLAIN ANALYZE SELECT parse_hex(n||'c30d040703020095dbb3d3746d096dd23b01c59bcbc7a4320f571511f61f3ea3def0e55404204a274500224927421bd5a8344a56316b909ef3af276b585622f1c9b7ca13563ee6fe88f4ddbe') FROM generate_series(1,1000) s(n);
Execution time: 640.031 ms

whereas for hex2dec I get

Execution time: 2354.616 ms

So my function is faster (for this size of input), I don't know if it mostly because of the better asymptotic complexity of binary splitting or the fact that it process 15 hex digits at a time (instead of 1).

Upvotes: 2

Related Questions