From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | Alvar Freude <alvar(at)a-blast(dot)org> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: bytea, index and like operator |
Date: | 2003-12-03 21:08:41 |
Message-ID: | 3FCE50D9.5060707@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alvar Freude | 2003-12-03 21:26:40 | Re: bytea, index and like operator |
Previous Message | Alvar Freude | 2003-12-03 17:20:48 | bytea, index and like operator |