Postgresql Convert bit varying to integer

Question

Searched the postgresql docs http://www.postgresql.org/docs/8.4/interactive/functions-bitstring.html for information on converting bit varying to integer

But couldnt' find any info.

select '011111'::bit(4)::varbit(4)::integer as varbit

Appreciate your response.

score 8 · Accepted Answer · edited May 23 '17 at 12:25

8

One way:

SELECT b, lpad(b::text, 32, '0')::bit(32)::int
FROM (
    VALUES
     ('01'::varbit)
    ,('011111')
    ,('111')
 ) t (b);

Result:

b      | lpad
-------+------
01     |    1
011111 |   31
111    |    7

Related answer:

Convert hex in text representation to decimal number

edited May 23 '17 at 12:25

Community

1
1

answered Jul 29 '14 at 17:06

Erwin Brandstetter

605,456
145
1,078
1,228

Thanks but my intention was that I have a column of type bit varying and I wanted to convert it to integer – connectwithpalaniappan Jul 29 '14 at 17:09
@Pals: The cast `::bit(32)` was missing in my first draft. Fixed and added a demo. – Erwin Brandstetter Jul 29 '14 at 17:14
It is working only for 32 bits. What is the solution if I have more than 32 bits? – connectwithpalaniappan Jul 29 '14 at 17:29
@Pals: How would you convert that to a 4-byte integer, which cannot hold more than 32 bit of information? You *did* ask for [`integer`](http://www.postgresql.org/docs/current/interactive/datatype-numeric.html). The next step is `bigint` with 64 bit. – Erwin Brandstetter Jul 29 '14 at 17:41

ramazan polat · Answer 2 · 2019-06-20T05:59:14.410

Let's say our table has 3 columns and 3 rows. We can simulate it with:

select *
from (
    values ('row1', 1::int, 12::bit(8)::varbit), 
           ('row2', 2::int, 23::bit(8)::varbit), 
           ('row3', 3::int, 34::bit(8)::varbit)
) as T(A,B,C);

As you can see, first columns is varchar , second is int and the third is varbit.

Let's convert third column to int:

select C::bit(8)::int 
from (
    values ('row1', 1::int, 12::bit(8)::varbit), 
           ('row2', 2::int, 23::bit(8)::varbit), 
           ('row3', 3::int, 34::bit(8)::varbit)
) as T(A,B,C);
==C==
  12
  23
  34

The point is, you have to convert it to bit(n) first, then you can convert bit(n) to varbit. The same thing is also true for int to varbit.

Postgresql Convert bit varying to integer

2 Answers2

Linked