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

Re: Relational operators

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Pieter-Jan Savat <pieterjan(dot)savat(at)barclab(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Relational operators
Date: 2005-06-27 14:52:45
Message-ID: 20050627073547.Q3841@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Mon, 27 Jun 2005, Pieter-Jan Savat wrote:

> 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?

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.


In response to

pgsql-bugs by date

Next:From: Robert GrabowskiDate: 2005-06-27 15:07:00
Subject: BUG #1733: Function visibility in transactions error
Previous:From: Michael FuhrDate: 2005-06-27 14:12:04
Subject: Re: process crash when a plpython function returns unicode

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