Re: bytea, index and like operator

Alvar Freude wrote:
> PostgreSQL 7.3.4 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4
>
> begin;
> create table test (b bytea);
> create index tst_idx on test(b);
> insert into test values ('\001abc\006');
> insert into test values ('\001xabc\006');
> insert into test values ('\001\002abc\006');
> insert into test values ('\000\001\002abc\006');
> insert into test values ('\002\003abc\006');

Note that bytea input strings should be escaped with doubled
backslashes, because the string literal parser consumes 1 layer, and the
byteain function consumes another. See:
http://www.postgresql.org/docs/7.3/static/datatype-binary.html

In the strings above, the string literal parser will turn, e.g., "\001"
into the single octet '\1' anyway, and byteain will accept it just fine.
However "\000" will become '\0', and since byteain requires a null byte
terminator, you are actually inserting an empty string into test.b for
that row:

regression=# select b, b = '' from test;
b | ?column?
-----------------+----------
\001abc\006 | f
\001xabc\006 | f
\001\002abc\006 | f
| t
\002\003abc\006 | f
(5 rows)

> select * from test where b like '\001%';

This is weird. I'm sure it worked at one time -- will research.

Joe