1

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.

Kurt Roeckx
  • 173
  • 1
  • 5

1 Answers1

1

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Yes, I stored it the wrong way for some of the data. I've send it as \\x30 as you would normally instead of as \\\\x30 when importing it in a bytea[] using COPY. But only a small fraction of the data is invalid. I was hoping to fix this just using SQL commands instead of exporting the data and importing it again. – Kurt Roeckx Aug 25 '20 at 07:48