Re: type cast from bytea to varchar or whatever

Michael Monnerie <michael(dot)monnerie(at)is(dot)it-management(dot)at> writes:
> in dbmail there's a query that worked until postgresql 8.2 (i stripped
> the unneeded parts for other tables):

> SELECT k.messageblk FROM dbmail_messageblks k WHERE k.messageblk ILIKE
> '%multipart/encrypted%';

> In 8.3, you get an error

There has *never* been a Postgres release that accepted bytea ILIKE
something. I'm not sure what you were really doing before, but that
wasn't it.

You could possibly get what you want by explicitly casting the bytea
value to text. But since that would have been needed (and was not
available) in earlier versions too, I'm a bit mystified by your claim
that this used to work.

I wouldn't really recommend relying on the cast to text, either,
as that has significant probability of not doing what you want
in 8.5 and above :-(. CVS HEAD does this:

regression=# select 'abcd'::bytea::text;
text
------------
\x61626364
(1 row)

regards, tom lane