Convert long Postgres bytea to decimal

Question

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?

[Conversion between hex and dec numbers](http://postgres.cz/wiki/PostgreSQL_SQL_Tricks_II#Conversion_between_hex_and_dec_numbers) (You need the "Variant that is able to handle arbitrary length input") — Abelisto, Sep 14 '16 at 21:06

redneb · Accepted Answer · 2016-09-14T21:32:20.000

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).

Looks great! I was still debugging my own solution, so I'll throw it away and take yours. :-) — Paul A Jungwirth, Sep 14 '16 at 21:37
@PaulAJungwirth And, ta-dam, `create or replace function hex2num_p(p_input text) returns numeric language plpythonu immutable strict as $$ return int(p_input, 16) $$;` - `Execution time: 29.971 ms` (for the `parse_hex` on the same data `Execution time: 2804.940 ms`) Just in case if we cares about speed :) — Abelisto, Sep 14 '16 at 22:10
29ms is much more reasonable, isn't it? Shelling out to python is lovely. :-) — Paul A Jungwirth, Sep 14 '16 at 23:29

Convert long Postgres bytea to decimal

1 Answers1