Double encoded bytea in PostgreSQL

Question

I'm storing binary data in a bytea field, but have during the import converted it twice to hex. How can I undo the double encoding?

My binary file starts with the character "0". In hex that's the character 30. In psql I expect to see the string that starts with \x30, since it will display it to me in hex by default. But what I see is that it starts with \x783330, where "78" is hex for "x", "33" from "3", and "30" for "0". So it's saying the stored string is: x30.

I can make it worse by casting text to a bytea, like encode(data, 'hex')::bytea, which will then turn it into \x373833333330, but I can't find a way to do the reverse. If I try decode(data::text, 'hex') it will complain about '' is not a valid hex string. If I use decode(substring(data::text) from 3), 'hex'), I get back my original string.

Laurenz Albe Laurenz Albe · Accepted Answer · 2020-08-25T02:09:15

You probably stored the bytea the wrong way.

If you INSERT a hexadecimal string into a bytea, it is interpreted as a string and not as hexadecimal digits unless you prepend it with \x.

See

SELECT 'DEADBEEF'::bytea, '\xDEADBEEF'::bytea;

       bytea        |   bytea    
--------------------+------------
 \x4445414442454546 | \xdeadbeef
(1 row)

When you use a program to insert a bytea, there are also ways to directly insert binary data; how that is done depends on the API you are using.

Double encoded bytea in PostgreSQL

1 Answers