Re: bytea, index and like operator

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

- -- Joe Conway <mail(at)joeconway(dot)com> wrote:

> 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

hmmm, but there remains some confusing stuff. Perhaps I made some other
mistake, but:

I've a filled table with bytea strings (converted from former text column),
and want to make a "like" comparison on it. So, now I'll make double
backslashes for each byte: like doesn't select the rows, when a index
exists. With "=" one row is selected. When the index is deleted, the
correct three rows are returned.

See below. Whats going wrong?

The same happens in my application, when the strings are given unescaped.

db=> SELECT bytea_col FROM table WHERE bytea_col like '\\001\\012\\010%';
bytea_col
- -----
(0 rows)

db=> SELECT bytea_col FROM table WHERE bytea_col = '\\001\\012\\010';
bytea_col
- --------------
\001\012\010
(1 row)

db=> SELECT bytea_col FROM table WHERE bytea_col like '\\001\\012\\010%';
bytea_col
- -----
(0 rows)

db=> begin;
BEGIN
db=> drop index table_bytea_col_idx;
DROP INDEX
db=> SELECT bytea_col FROM table WHERE bytea_col like '\\001\\012\\010%';
bytea_col
- --------------------------
\001\012\010\001\001\001
\001\012\010
\001\012\010\001\001\002
(3 rows)

db=> rollback;
ROLLBACK
db=> SELECT bytea_col FROM table WHERE bytea_col like '\\001\\012\\010%';
bytea_col
- -----
(0 rows)

db=> SELECT bytea_col FROM table WHERE bytea_col like '\\001\\012\\010';
bytea_col
- -----
(0 rows)

Ciao
Alvar

- --
** Alvar C.H. Freude -- http://alvar.a-blast.org/
** Berufsverbot? http://odem.org/aktuelles/staatsanwalt.de.html
** ODEM.org-Tour: http://tour.odem.org/
** Informationsgesellschaft: http://www.wsis-koordinierungskreis.de/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQE/znDNOndlH63J86wRAtAyAKDJYq/KPSH7W4rJvO+VJQGe0OQi3wCfQOfr
HmWZiQdc4MW5JecTG0dqwSg=
=h3ag
-----END PGP SIGNATURE-----