Re: Specifying text to substitute for NULLs in selects

Mike Toews wrote:
> Keep in mind that you can't mix data types, like 'NaN'::text and
> 32.3::float in the result.

oh yeah, regarding mixing data types (in regards to the first post)...

A good exception is that you can use 'NaN' for floating point data
types, so:

SELECT COALESCE(myval, 'NaN') as myval FROM foo;

where "myval" is a field with a floating-point data type. This maneuver
is sometimes preferred in some aggregates like sum() where you don't
want to take sums on incomplete sets since NULL is counted as 0 whereas
a single NaN value forces the resulting sum to be NaN.

There are other special floats like 'Infinity' and '-Infinity', which
can also be coalesced in for NULL float values:
http://www.postgresql.org/docs/current/interactive/datatype-numeric.html

-Mike