Hi,

I was wondering if the following issue is in fact a bug, or just
inconvenient behaviour...

say a table looks like this:

table
---
c (varchar)
'20'
'0'
'-10'
'klj'
'> 5'
'qwerty'
'< 6'

The query select * from table where c < 5 will return for instance the
tuple containing '20'.
In other words, postgresql 8.0 will not automatically cast the results
to a numeric value and only take into account those tuples that can be
cast (not 'klj' nor 'qwerty') ... unlike some other databases ....

Any (standard) solutions to this problem?

-greetz, pj


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Mon, 27 Jun 2005, Pieter-Jan Savat wrote:

I don't think so in general, since afaik SQL92 makes the comparison c < 5
illegal (I don't see anywhere that says that numerics and character
strings are comparable). I believe the only reason the above works in
PostgreSQL is that both int and varchar can implicitly cast to text
currently; if the int->text cast were made non-implicit it would error
trying to find a valid operator.

I can think of ways to get the requested behavior in PostgreSQL that
involve changing the query, but those wouldn't be portable to something
else. You could also make a function that did the cast if possible or
returned NULL and make appropriate operators between the types, however
those semantics are pretty strange, and I'm not sure if it's a good idea
to change them wholesale for the whole system.


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org