type cast from bytea to varchar or whatever

--nextPart1666811.Dq6iMQgs0H
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Dear list,

in dbmail there's a query that worked until postgresql 8.2 (i stripped=20
the unneeded parts for other tables):

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

In 8.3, you get an error=20

[ERROR: operator does not exist: bytea ~~* unknown LINE 1: ...55) ILIKE=20
'%multipart/encrypted%' OR k.messageblk ILIKE '%mu...
^
HINT: No operator matches the given name and argument type(s). You=20
might need to add explicit type casts.

OK, but how to cast that? messageblk is of type "bytea", so I can't use=20
these:

SELECT k.messageblk FROM dbmail_messageblks k WHERE k.messageblk ILIKE=20
'%multipart/encrypted%'::bytea;
SELECT k.messageblk FROM dbmail_messageblks k WHERE =20
k.messageblk::varchar ILIKE '%multipart/encrypted%';

any ideas?

(It should just convert, in case of special chars ignore them. The=20
contect are mails, so normally text. Don't care about non-ascii here.)

mfg zmi
=2D-=20
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0660 / 415 65 31 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4


--nextPart1666811.Dq6iMQgs0H
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.9 (GNU/Linux)

iEYEABECAAYFAkqousAACgkQzhSR9xwSCbTf4gCeO/B/lSwtv9hWHIrNfdvi C678
5tgAoL2gceZwdY1dGrBy38wcd+5BYQMX
=jnVp
-----END PGP SIGNATURE-----

--nextPart1666811.Dq6iMQgs0H--

Re: type cast from bytea to varchar or whatever

Michael Monnerie 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

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: type cast from bytea to varchar or whatever

On Donnerstag 10 September 2009 Tom Lane wrote:
> There has *never* been a Postgres release that accepted bytea ILIKE
> something. =A0I'm not sure what you were really doing before, but that
> wasn't it.

You're right: That query was generated on a specific search, and doesn't=20
work. It's a bug that needs a fix.

I currently have no idea how to cast bytea to text. For this purpose of=20
searching through mails, it would be enough for postgres to treat the=20
bytea as text, and just search through it. You can only find ascii chars=20
then, but that's a limitation one can live with. The only other way=20
would be to retrieve the whole stuff and search within a C program, but=20
that would mean retrieving a lot of data from the DB that's not needed -=20
bad for performance. Is there no "override" to ignore bytea encoding and=20
search it ascii-like?

mfg zmi
--=20
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0660 / 415 65 31 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: type cast from bytea to varchar or whatever

On Sonntag 13 September 2009 Michael Monnerie wrote:
> On Donnerstag 10 September 2009 Tom Lane wrote:
> > There has *never* been a Postgres release that accepted bytea ILIKE
> > something. =A0I'm not sure what you were really doing before, but
> > that wasn't it.
>
> You're right: That query was generated on a specific search, and
> doesn't work. It's a bug that needs a fix.
>
> I currently have no idea how to cast bytea to text. For this purpose
> of searching through mails, it would be enough for postgres to treat
> the bytea as text, and just search through it. You can only find
> ascii chars then, but that's a limitation one can live with. The only
> other way would be to retrieve the whole stuff and search within a C
> program, but that would mean retrieving a lot of data from the DB
> that's not needed - bad for performance. Is there no "override" to
> ignore bytea encoding and search it ascii-like?

Ping?

mfg zmi
--=20
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0660 / 415 65 31 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: type cast from bytea to varchar or whatever

Michael Monnerie wrote:
> On Sonntag 13 September 2009 Michael Monnerie wrote:

>> I currently have no idea how to cast bytea to text. For this
>> purpose of searching through mails, it would be enough for
>> postgres to treat the bytea as text, and just search through it.
>> You can only find ascii chars then, but that's a limitation one
>> can live with.

> Ping?

Nothing here works for you?:

http://www.postgresql.org/docs/8.4/interactive/functions-str ing.html

I would have thought convert_from, possibly combined with convert,
would do it.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: type cast from bytea to varchar or whatever

On Dienstag 15 September 2009 Kevin Grittner wrote:
> http://www.postgresql.org/docs/8.4/interactive/functions-str ing.html
> =A0
> I would have thought convert_from, possibly combined with convert,
> would do it.

That function exists since 8.3, I'm on 8.1 on my server. Will forward it=20
to others, and try to push my server to 8.3 quickly.
Thank you for the hint!

mfg zmi
--=20
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0660 / 415 65 31 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin