Re: How to handle 'not a number' in postgresql

On Fri, 4 Jan 2008, Michael Glaesemann wrote:

> It's not unknown: it's known to be something other than a number, which is
> not the same as unknown.

Michael,

Perhaps I mis-interpreted the original message. If a measurement is out of
the instrument's range it means that the value is unknown. For example, a
numeric value that is below the instrument's detection limit. It may well be
that the instrument software vendor chose to return NaN rather than NULL in
this case, but from an analytical perspective it's an unknown numeric value
rather than a string or boolean.

This issue comes up frequently in water (or air) quality measurements
where the measured value of a heavy metal is below analytical detection
limits. Some regulatory agencies want that number to be reported as an
actual numeric value rather than as "below detection limits," or NULL for
"unknown value."

The same holds when the equipment mal-functions. The result would be a
number if the equipment worked, but that does not make it a non-number
otherwise. Maybe we have a difference in semantics that is dependent upon
the application.

Rich

--
Richard B. Shepard, Ph.D. | Integrity Credibility
Applied Ecosystem Services, Inc. | Innovation
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863