How to cast type bytea to double precision

Question

I have a database column which type is bytea. It contains floats converted as byte array (4 bytes per one float) and encoding is Escape. I'm be able to retrieve corresponding bytea string using substring function.

My question is how can I convert bytea string to float inside a SQL function. Earlier I converted to float in C# side. I used dataReader.getByte method to retrieve bytes and then Converted to float using BitConverter.ToSingle (.Net build in class) method.

Now I can't use intermediate component as Npqsql driver. I want SQL to directly convert bytea into floats and return the corresponding number when execute a query from 3rd party application.

Thanks Amila

score 5 · Accepted Answer · edited Jul 26 '12 at 22:14

For this purpose the best possible solution is to convert into bytes using IEEE754-1985 standard using SQL commands.

First it is required to check for special cases defined by IEEE754-1985 standard. Then just follow the standard algorithm to convert if it is not in any special cases. Sample code is below.

Inputs are bytea_value bytea, is_little_endian boolean then divide into 4 bytes as below:

  byte_array[0]:= get_byte(bytea_value, 0);
  byte_array[1]:= get_byte(bytea_value, 1);
  byte_array[2]:= get_byte(bytea_value, 2);
  byte_array[3]:= get_byte(bytea_value, 3);

Then get the binary value by considering little endian or big endian

IF is_little_endian THEN
        binary_value:= byte_array[0]::bit(8) || byte_array[1]::bit(8) || byte_array[2]::bit(8) || byte_array[3]::bit(8);
    ELSE
        binary_value:= byte_array[3]::bit(8) || byte_array[2]::bit(8) || byte_array[1]::bit(8) || byte_array[0]::bit(8); 
    END IF;

Now check for special cases:

IF binary_value = '00000000000000000000000000000000' OR binary_value = '10000000000000000000000000000000' THEN -- IEEE754-1985 Zero
        return 0.0;
    END IF;

sign := substring(binary_value from 1 for 1);
    exponent := substring(binary_value from 2 for 8);
    mantissa := substring(binary_value from 10 for 23); 

    IF exponent = '11111111' THEN
        IF mantissa = '00000000000000000000000' THEN   -- IEEE754-1985 negative and positive infinity
            IF sign = '1' THEN                    
                return '-Infinity';                    
            ELSE                    
                return 'Infinity';  
            END IF;                  
        ELSE
          return 'NaN'; -- IEEE754-1985 Not a number
        END IF; 
    END IF;

If it does not belong to any special cases just convert it as below:

exp := exponent::int;

    IF exp > 126 THEN
     exp := exp - 127;
    ELSE
     exp:= -exp;
    END IF;

    WHILE mantissa_index < 24 LOOP
        IF substring(mantissa from mantissa_index for 1) = '1' THEN
            result := result + power(2, -(mantissa_index));
        END IF;
        mantissa_index = mantissa_index + 1;
    END LOOP;

    result := result * power(2, exp);

    IF(sign = '1') THEN
        result = -result;
    END IF;

    return result;

score 2 · Answer 2 · answered Oct 01 '18 at 23:58

I have the same task to convert data from HW layer stored in PostgreSQL bytea field in 32-bit wide single precision floating point number (IEEE 754) to PostgreSQL friendly data. Resolved using the previous answer with minor patching.

Since I do not find a better working solution, I publish my result:

CREATE OR REPLACE FUNCTION public.get_bytea_to_double(b bytea, offs int)
  RETURNS double precision AS
$BODY$
DECLARE
barray0 bit(8);
barray1 bit(8);
barray2 bit(8);
barray3 bit(8);
binary_value bit(32);
sign character(1);
exponent bit(8);
exp smallint;
mantissa bit(23);
mantissa_index int;
result double precision;
BEGIN

barray0:= get_byte_n(b,offs+0)::bit(8);
barray1:= get_byte_n(b,offs+1)::bit(8);
barray2:= get_byte_n(b,offs+2)::bit(8);
barray3:= get_byte_n(b,offs+3)::bit(8);

--true endian assemble
binary_value:= barray3 || barray2 || barray1 || barray0;
--RAISE NOTICE 'BINVAL:%', binary_value;

IF binary_value = '00000000000000000000000000000000' OR binary_value = '10000000000000000000000000000000' THEN -- IEEE754-1985 Zero
   return 0.0;
END IF;

sign := substring(binary_value from 1 for 1);
exponent := substring(binary_value from 2 for 8);
mantissa := substring(binary_value from 10 for 23);

--RAISE NOTICE 'MANTISSA-BIT:%', mantissa;
--RAISE NOTICE 'EXP-BIT:%', exponent;

IF exponent = '11111111' THEN
   IF mantissa = '00000000000000000000000' THEN   -- IEEE754-1985 negative and positive infinity
      IF sign = '1' THEN
         return '-Infinity';
      ELSE
         return 'Infinity';
      END IF;
   ELSE
      return 'NaN'; -- IEEE754-1985 Not a number
   END IF;
END IF;

exp := exponent::int;
--RAISE NOTICE 'EXP:%', exp;

IF exp > 126 THEN
   exp := exp - 127;
ELSE
   exp:= -exp;
END IF;

result:=1.0;
mantissa_index:=1;
WHILE mantissa_index < 24 LOOP
   IF substring(mantissa from mantissa_index for 1) = '1' THEN
      result := result + power(2, -(mantissa_index))::double precision;
   END IF;
   mantissa_index = mantissa_index + 1;
END LOOP;

result := result * power(2, exp)::double precision;

IF (sign = '1') THEN
   result = -result;
END IF;

return result;

END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

And you need one more function to getting HEX bytes from BYTEA by it's number:

CREATE OR REPLACE FUNCTION public.get_byte_n(
    bytea,
    integer)
  RETURNS integer AS
$BODY$
           declare r int;
           declare t text;            
           begin 
             t:=encode(substring($1 from (2*$2)+1 for 2),'escape');
             execute E'select x\''||t|| E'\'::integer' into r; 
             return r; 
           end
           $BODY$
  LANGUAGE plpgsql IMMUTABLE
  COST 100;

score 0 · Answer 3 · answered Mar 02 '23 at 10:04

You can use plperlu extension (and have all the features of the perl language)

CREATE OR REPLACE FUNCTION devices.strHexToFloat(character varying)
 RETURNS character varying
 LANGUAGE plperlu
AS $function$
    use utf8;
    use open ':encoding(utf8)';
    binmode(STDOUT, ":utf8");

    $hex = pack( 'H*', $_[0] );
    $ff = unpack('f', reverse($hex));

    return $ff ;

$function$
;

select devices.strHexToFloat('3d59da6a');

result: 0.0531868115067482

How to cast type bytea to double precision

3 Answers3

Linked