Thread: Can not match 0 on bytea

Hi!

Data type of table1.c1 is bytea. That column stores binary data. The following matchings do not work. What is the right
syntax?

TIA
CN
---------------
select c1 ~ E'\000' from table1;
select c1 LIKE E'%\000%' from table1;

ERROR:  invalid byte sequence for encoding "UTF8": 0x00
HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is
controlledby "client_encoding". 

     seiliki@so-net.net.tw wrote:

> Data type of table1.c1 is bytea. That column stores binary data. The
> following matchings do not work. What is the right syntax?
>
> TIA
> CN
> ---------------
> select c1 ~ E'\000' from table1;
> select c1 LIKE E'%\000%' from table1;

selection position(E'\\000'::bytea in c1) from table1;
The value is 0 when there is no match and >0 otherwise.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

seiliki@so-net.net.tw writes:
> Data type of table1.c1 is bytea. That column stores binary data. The following matchings do not work. What is the
rightsyntax? 

> TIA
> CN
> ---------------
> select c1 ~ E'\000' from table1;
> select c1 LIKE E'%\000%' from table1;

> ERROR:  invalid byte sequence for encoding "UTF8": 0x00

The reason that doesn't work is that E'\000' is initially a text
literal, with the backslash sequence being processed by the string
literal parser; and a zero byte isn't allowed in text.

Try it with E'\\000'.  What this gives rise to is a text constant
containing the four characters \ 0 0 0, and then when that gets
converted to bytea, another round of backslash processing will happen
to produce the (legal) bytea constant with a single zero byte.

            regards, tom lane