Convert a bytea into a binary string

postgresql binary-data bytea
GPif picture GPif · May 6, 2019 · Viewed 8.2k times · Source

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".)

Answer

Erwin Brandstetter picture Erwin Brandstetter · May 6, 2019

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:

Related questions

Use binary COPY table FROM with psycopg2

I have tens of millions of rows to transfer from multidimensional array files into a PostgreSQL database. My tools are Python and psycopg2. The most efficient way to bulk instert data is using copy_from. However, my data are mostly 32…

Read More
Postgres bytea column is returning string (char array) instead of byte array

I have been using C# to write a concrete provider implementation for our product for different databases. W/out getting into details, one of the columns is of byte array type (bytea in postgres - due to the preferences bytea …

Read More
How to store videos in a PostgreSQL database?

I am storing image files (like jpg, png) in a PostgreSQL database. I found information on how to do that here. Likewise, I want to store videos in a PostgreSQL database. I searched the net - some say one should …

Read More