Обсуждение: type cast from bytea to varchar or whatever
Dear list, 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 [ERROR: operator does not exist: bytea ~~* unknown LINE 1: ...55) ILIKE '%multipart/encrypted%' OR k.messageblk ILIKE '%mu... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. OK, but how to cast that? messageblk is of type "bytea", so I can't use these: SELECT k.messageblk FROM dbmail_messageblks k WHERE k.messageblk ILIKE '%multipart/encrypted%'::bytea; SELECT k.messageblk FROM dbmail_messageblks k WHERE k.messageblk::varchar ILIKE '%multipart/encrypted%'; any ideas? (It should just convert, in case of special chars ignore them. The contect are mails, so normally text. Don't care about non-ascii here.) mfg zmi -- // 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
Вложения
Michael Monnerie <michael.monnerie@is.it-management.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
On Donnerstag 10 September 2009 Tom Lane wrote: > 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'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? mfg zmi -- // 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
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. I'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 -- // 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
Michael Monnerie <michael.monnerie@is.it-management.at> 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-string.html I would have thought convert_from, possibly combined with convert, would do it. -Kevin
On Dienstag 15 September 2009 Kevin Grittner wrote: > http://www.postgresql.org/docs/8.4/interactive/functions-string.html > > 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 to others, and try to push my server to 8.3 quickly. Thank you for the hint! mfg zmi -- // 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