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