Re: Fwd: Bug#249036: postgresql: zero bytes cannot be entered

Martin Pitt wrote:
> Hi PostgreSQL hackers!
>
> We recently got the Debian bug report below. Does anybody have an idea about that?
>
> Thanks and have a nice day!
>
> if i issue the following query:
> $ select decode(encode('\001\000\001'::bytea,'hex'),'hex');
> the result will always be '\001' and not '\001\000\001' as assumable.
>
> i think the string is handled zero-delimited and not with its given
> size,
> and i think in this case this is not the wanted behaviour because to
> enter any bytea with zero bytes one will have to use
> decode('my string in hex', 'hex'), only then the zero bytes will be
> stored
> correctly.

The issue is that bytea needs double-backslashes because single
backslashes are processed by the parser into strings:

test=> select decode(encode('\001\000\001'::bytea,'hex'),'hex');
decode
--------
\001
(1 row)

test=> select decode(encode('\\001\\000\\001'::bytea,'hex'),'hex');
decode
--------------
\001\000\001
(1 row)

This is all in the documentation.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073