Skip site navigation (1) Skip section navigation (2)

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

From: Bill Moran <wmoran(at)collaborativefusion(dot)com>
To: "Hugo Rafael Lesme Marquez" <hugolesme(at)hotmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #4055: Using Like in PostgreSQL 8.2.7 and 8.3.1
Date: 2008-03-25 13:14:27
Message-ID: 20080325091427.faba196a.wmoran@collaborativefusion.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: Bill MoranDate: 2008-03-25 14:18:36
Subject: Re: BUG: PG do not use index
Previous:From: Bruce MomjianDate: 2008-03-25 13:04:55
Subject: Re: why provide cross type arithmetic operators

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group