Problem with select and null

[Frank Millman]

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

[Michael Paesold]

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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

"Frank Millman" <fr***@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org