Re: Problem with select and null

"Frank Millman" <frank(at)chagford(dot)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