Re: BUG #4055: Using Like in PostgreSQL 8.2.7 and 8.3.1

In response to "Hugo Rafael Lesme Marquez" <hugolesme(at)hotmail(dot)com>:

>
> The following bug has been logged online:
>
> Bug reference: 4055
> Logged by: Hugo Rafael Lesme Marquez
> Email address: hugolesme(at)hotmail(dot)com
> PostgreSQL version: 8.2.7 and 8.3.1
> Operating system: Windows 2003 Server SP2
> Description: Using Like in PostgreSQL 8.2.7 and 8.3.1
> Details:
>
> Using PostgreSQL Database 8.2.7 and 8.3.1
>
> With PostgreSQL 8.2.7
>
> select * from movimientos where idmovimientos like '178'
>
> returns
>
> 178; "2008-02-16"; 4700.00; 2; 1; "2008-02-22 16:39:09.14"; 2; "maricel"; 2
>
> Ok!!!
>
> With PostgreSQL 8.3.1
>
> select * from movimientos where idmovimientos like '178'
>
> returns
>
> ERROR: operator does not exist: bigint ~~ unknown
> LINE 1: select * from movimientos where idmovimientos like '178'
> ^
> HINT: No operator matches the given name and argument type(s). You might
> need to add explicit type casts.
>
> ********** Error **********
>
> ERROR: operator does not exist: bigint ~~ unknown
> SQL state: 42883
> Dica: No operator matches the given name and argument type(s). You might
> need to add explicit type casts.
> Caracter: 47
>
> What can I do to resolve?

8.3 is fussier about type matching than 8.2 was. The query above is
casting idmovimientos to TEXT to do the comparison (since LIKE is
pointless on a BIGINT value). 8.3 doesn't do this automatically.

If you have a legit reason to do the above comparison, cast that column
to TEXT. Although I agree with Alvaro that the query (as provided) is
pretty pointless.

You could also adjust the pg_cast table to make the cast implicit, which
would mimic the 8.2 behaviour.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran(at)collaborativefusion(dot)com
Phone: 412-422-3463x4023