How to convert bit type to bytea type in Postgresql

Question

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.

do you analyze it ?postgresql.org/docs/9.3/static/functions-binarystring.html? — OMANSAK
– OMANSAK, Commented Sep 14, 2018 at 7:58
Yes,I've already read it.But, I could not find any clue to resolve this problem. — Boblishus
– Boblishus, Commented Sep 14, 2018 at 8:01
Why did you want to save as bytea and what is your expect result from your query — D-Shih
– D-Shih, Commented Sep 14, 2018 at 8:04
Originally the data is stored as bytea type in database .To do bit-wise operation, it is converted from bytea to bit. However the calculated bit can not be reversed to bytea. — Boblishus
– Boblishus, Commented Sep 14, 2018 at 8:14

klin · Accepted Answer · 2018-09-14 08:32:55Z

3

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

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

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

answered Sep 14, 2018 at 8:32

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Boblishus Over a year ago

This seems to be correct.However,in case of 300 digits bit, error occured ERROR: integer out of range. I want to do bit-wise operation with over 1000 digits bit.

Boblishus Over a year ago

The calculated bits may have to be divided into a small range bits.And then,convert the bits to bytea like you suggested.

klin Over a year ago

One way is to divide text representation into 32 hex digits parts (with bigint). You can also try pg-bignum extension.

Peter Krauss Over a year ago

Ugly PostgreSQL, even nowadays (2019!) no option to efficient cast from varbit to bytea!?

Matt Whitlock · Accepted Answer · 2024-02-29 22:52:40Z

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.

Collectives™ on Stack Overflow

How to convert bit type to bytea type in Postgresql

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related