Convert a bytea into a binary string

Question

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

Insolvable

Answer 1

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:

• Convert hex in text representation to decimal number

Athematic

Is there any hidden optimization in PostgreSQL or does this convert twice? – Type

Answer 2

demo:db<>fiddle

You could put the following code into a function:

WITH byte AS (   -- 1
    SELECT E'\\xDEADBEEF'::bytea as value
)
SELECT
    string_agg(      -- 5
        get_byte(value, gs)::bit(8)::text -- 4
        , ''
    )
FROM 
    byte,
    generate_series(        -- 3
        0, 
        length(value) - 1   -- 2
    ) gs

I demonstrated the development of the query within the fiddle.

1. The WITH clause encapsulates the bytea value for double usage in further code
2. length() calculates the binary length of the bytea value
3. generate_series() creates a list from 0 to length - 1 (0 - 3 in my example)
4. get_byte() takes the bytea value a second time and gives out the byte at position gs (the previous calculated values 0-3). This gives an integer representation of the the byte. After that the cast to bit(8) type converts the result of this function to its binary representation (1 byte = 8 bit)
5. string_agg() finally aggregates all for binary strings into one. (taking its text representations instead of bit type, with no delimiters)

---

A function could look like this:

CREATE OR REPLACE FUNCTION to_bit(value bytea) RETURNS SETOF text AS 
$$
BEGIN
    RETURN QUERY 
        SELECT
            string_agg(get_byte(value, gs)::bit(8)::text, '')
        FROM 
            generate_series(0, length(value) - 1) gs;
END;
$$ LANGUAGE plpgsql;

After that you could call it:

SELECT to_bit(E'\\xDEADBEEF'::bytea)

---

You could try it using get_bit() instead of get_byte(). This safes you the ::bit(8) cast but of course you need to multiply the length with factor 8 indeed.

The resulting bit string has another bit order but maybe it fits your use case better:

WITH byte AS (
    SELECT E'\\xDEADBEEF'::bytea as value
)
SELECT
    string_agg(get_bit(value, gs)::text, '')
FROM 
    byte,
    generate_series(0, length(value) * 8 - 1) gs

demo:db<>fiddle

Sphere