Обсуждение: type cast from bytea to varchar or whatever

Поиск
Список
Период
Сортировка

type cast from bytea to varchar or whatever

От
Michael Monnerie
Дата:
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


Вложения

Re: type cast from bytea to varchar or whatever

От
Tom Lane
Дата:
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

Re: type cast from bytea to varchar or whatever

От
Michael Monnerie
Дата:
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


Re: type cast from bytea to varchar or whatever

От
Michael Monnerie
Дата:
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


Re: type cast from bytea to varchar or whatever

От
"Kevin Grittner"
Дата:
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

Re: type cast from bytea to varchar or whatever

От
Michael Monnerie
Дата:
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