Menu
I need to decode a base64 string and take a chunk of binary.
Is there a SQL function in Postgres to simply convert a bytea into a binary string representation?
(Like "00010001010101010".)
986
In Java, we can use Integer. toBinaryString(int) to convert a byte to a binary string representative. Review the Integer. toBinaryString(int) method signature, it takes an integer as argument and returns a String.
The bytea data type allows the storage of binary strings or what is typically thought of as “raw bytes”. Materialize supports both the typical formats for input and output: the hex format and the historical PostgreSQL escape format. The hex format is preferred.
The BYTEA data type stores a binary string in a sequence of bytes. Digital images and sound files are often stored as binary data. EDB Postgres Advanced Server can store and retrieve binary data by way of the BYTEA data type.
Byte arrays mostly contain binary data such as an image. If the byte array that you are trying to convert to String contains binary data, then none of the text encodings (UTF_8 etc.) will work.
If your Postgres installation runs with the default setting bytea_output = 'hex', there is a very simple hack:
SELECT right(bytea_col::text, -1)::varbit;
Example:
SELECT right((bytea '\xDEADBEEF')::text, -1)::varbit;
Result:
'11011110101011011011111011101111'
right(text, -1) is just the cheapest way to remove the leading backslash from the text representation.
varbit (standard SQL name bit varying) is for bit strings of arbitrary length. Cast the result to text or varchar if you like.
Related, with explanation:
159
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
