Friday, November 25, 2011

Postgresql - Handling 'NaN'::float -> crosstab function

Today AVR asked me: how do I compare against a NaN ? And I was like you mean to an AND negated ? LOL.
It turns out that 'NaN' is a simbolic value meaning "Not a number".

In addition to ordinary numeric values, the numeric type allows the special value NaN, meaning "not-a-number". Any operation on NaN yields another NaN. When writing this value as a constant in an SQL command, you must put quotes around it, for example UPDATE table SET x = 'NaN'. On input, the string NaN is recognized in a case-insensitive manner.

Note: In most implementations of the "not-a-number" concept, NaN is not considered equal to any other numeric value (including NaN). In order to allow numeric values to be sorted and used in tree-based indexes, PostgreSQL treats NaN values as equal, and greater than all non-NaN values.

In addition to ordinary numeric values, the floating-point types have several special values:
Infinity
-Infinity
NaN

http://developer.postgresql.org/pgdocs/postgres/datatype-numeric.html

If you want to compare against the 'NaN' ("not a number") float value, then do an explicit cast to float: float('NaN').

http://stackoverflow.com/questions/5401455/is-there-a-database-independent-way-with-sqlalchemy-to-query-filtered-by-none

Here are some very good insights regarding this topic.

http://www.ivoa.net/internal/IVOA/InterOpOct2011Applications/null_nan-3.pdf

This issue arose due to the apparent fact that the function crosstab returns this symbolic constant.

In the end she did this:
CASE WHEN CHALCO = 'NaN'::float OR CHALCO IS NULL THEN 0 ELSE CHALCO END,
and seemed to work.

No comments:

Post a Comment