[FIXED] How to convert bit varying to numeric in postgresql

Issue

I am trying to convert bit varyingto numeric (decimal value) in postgresql

select cast('10110101110110010100110001101010100111011011010000000101'::bit varying as numeric);

I have tried the cast function but getting below error.

ERROR: cannot cast type bit varying to numeric LINE 1: select cast('10110101110110010100110001101010100111011011010... ^ ********** Error **********

ERROR: cannot cast type bit varying to numeric SQL state: 42846 Character: 8

Output should be : 5429503678976. Any suggestions

EDIT: I do not know the max value so can not use

select b'10110101110110010100110001101010100111011011010000000101'::bigint

Thanks

Solution

You could break it up into individual bits, convert those to the appropriate numeric values and then sum them together:

with data(d) as (VALUES (B'10110101110110010100110001101010100111011011010000000101') )
select sum(parts) FROM (
  SELECT get_bit(d, length(d) - n)::numeric * pow(2::numeric, n-1) as parts
  from data, generate_series(1, length(d), 1) g(n)
) sub
;
                sum
------------------------------------
 51185893014090757.0000000000000000
(1 row)

Note that this gives me the same answer as select b'10110101110110010100110001101010100111011011010000000101'::bigint; but it should work for larger values as well, where bigint would be out of range.

You could turn it into a function:

CREATE OR REPLACE FUNCTION bitstring_to_numeric(l_bit_string varbit) 
RETURNS numeric AS $$
  SELECT sum(parts) FROM (
    SELECT get_bit(l_bit_string, 
                   length(l_bit_string) - n)::numeric * pow(2::numeric, n-1) as parts
    from generate_series(1, length(l_bit_string), 1) g(n)
    ) sub
$$ LANGUAGE SQL
IMMUTABLE;

select bitstring_to_numeric(B'10110101110110010100110001101010100111011011010000000101');
        bitstring_to_numeric
------------------------------------
 51185893014090757.0000000000000000

Answered By - Jeremy

Answer Checked By - Mary Flores (PHPFixing Volunteer)