CODEWITHSUNDEEP
postgresql
Boblishus
Boblishus

Reputation: 97

How to convert bit type to bytea type in Postgresql

I want to convert bit type into bytea in postgresql. Like this.

select (b'1010110011001100' & b'1011000011110000')::bytea;

However, error occured

ERROR:  cannot cast type bit to bytea
LINE 1: select (b'1010110011001100' & b'1011000011110000')::bytea;

I just wanted to do an operation on bit strings and convert to bytea type.

Upvotes: 4

Views: 4832

Answers (2)

Matt Whitlock
Matt Whitlock

Reputation: 887

You can call the bit varying type's send function to get the external binary form of a bit string as a bytea and then pass that to the substr function to chop off the initial 4-byte bit length field.

SELECT substr(varbit_send(b'1010110011001100' & b'1011000011110000'), 5);

substr | \xa0c0

Note that the final byte will contain unused (cleared) bits if the bit length of the bit string was not a whole multiple of 8 bits.

Doing this will incur some extra copying in memory, so it's not as efficient as one might hope, but it's better than the other proposal of reinterpreting the bit string as an integer, encoding it into hex, and then decoding the hex into a bytea, as that causes leading zero nibbles to be dropped and fails when the number of significant nibbles is odd:

SELECT decode(to_hex((b'1010110011001100' & b'0101010011110000')::int), 'hex');

ERROR:  invalid hexadecimal data: odd number of digits

Unfortunately, you can't use the "external binary form" trick to go in the opposite direction, as base types' receive functions do not accept a bytea datum as an argument.

Upvotes: 0

klin
klin

Reputation: 121474

Convert the bit value to hex and use decode():

select decode(to_hex((b'1010110011001100' & b'1011000011110000')::int), 'hex')

 decode 
--------
 \xa0c0
(1 row)

Upvotes: 3

Related Questions