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
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
