Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem with select and null

Problem with select and null

From
"Frank Millman"
Date:
Hi all
 
'SELECT (null * null)' returns null
 
'SELECT (null - 0)' returns null
 
'SELECT ((null * null) - 0)' gives the following error -
ERROR: operator does not exist: "char" - integer
 
Why does this statement give an error? I would expect it to return null.
 
Under some circumstances my program generates the above select statement, so this is not an academic question.
 
Platform is PostgreSQL 7.4.1, running on Redhat 9.
 
Is this a bug? Is there any workaround? My workaround for now is to 'coalesce' to zero all the values which could return null. The result is zero instead of null, which is not what I want, but I can live with it in the short term.
 
TIA for any suggestions.
 
Frank Millman

Re: Problem with select and null

From
"Michael Paesold"
Date:
Frank Millman wrote:

> Hi all
>
> 'SELECT (null * null)' returns null
>
> 'SELECT (null - 0)' returns null
>
> 'SELECT ((null * null) - 0)' gives the following error -
> ERROR: operator does not exist: "char" - integer
>
> Why does this statement give an error? I would expect it to return null.

This does not seem to be a bug from my point of view. Postgres just doesn't
know what datatype these nulls should be. You can cast the null values to
integer (or float if you need):

SELECT ((null::integer * null::integer) - 0);
   -- returns null

Best Regards,
Michael Paesold

Re: Problem with select and null

From
Tom Lane
Date:
"Frank Millman" <frank@chagford.com> writes:
> 'SELECT (null * null)' returns null

IMHO you really ought to get an error from that.  It's just as type-free
as

    select ('FOO' * 'bar');

which currently gives a rather silly result.  The reason this happens
is that the type "char" (not to be confused with char) has arithmetic
operators, and we have an implicit cast from text to "char" so these
operators are able to suck in constructs that are probably user errors.
I have previously proposed removing these operators, which seem quite
useless anyway, but didn't get around to doing it for 8.0.

> Under some circumstances my program generates the above select statement, s=
> o this is not an academic question.

Figure out what type your program is expecting the null to be, and
explicitly cast it to that type.  For instance

    SELECT ((null::int * null::int) - 0);

            regards, tom lane