Problem with select and null

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

"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