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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073