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

I am new to this group so forgive me if this is the wrong area to post
in.

I have some field data which comes off of a data logger. In some cases
the data is out of bounds of logger or instrumentation. In which case
it is reported as 'NaN' for 'not a number.'

Is there such a value in postgresql? If not I will have to hack it in
with a boolean and would rather not do so.

TIA

On Fri, Dec 28, 2007 at 10:22:03AM -0800, thereverandpdawg wrote:
> I have some field data which comes off of a data logger. In some cases
> the data is out of bounds of logger or instrumentation. In which case
> it is reported as 'NaN' for 'not a number.'
>
> Is there such a value in postgresql? If not I will have to hack it in
> with a boolean and would rather not do so.

Did you try it?

# select 'nan'::float;
float8
--------
NaN
(1 row)

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
> -- John F Kennedy

On Fri, 28 Dec 2007, thereverandpdawg wrote:

> I have some field data which comes off of a data logger. In some cases
> the data is out of bounds of logger or instrumentation. In which case
> it is reported as 'NaN' for 'not a number.'
>
> Is there such a value in postgresql? If not I will have to hack it in
> with a boolean and would rather not do so.

It would be appropriate to convert NaN to NULL since the valid is
literally unknown.

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

On Jan 4, 2008, at 9:35 AM, Rich Shepard wrote:

> It would be appropriate to convert NaN to NULL since the valid is
> literally unknown.

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

*considers quoting Rumsfield, but common sense prevails*

Michael Glaesemann
michael(dot)glaesemann(at)myyearbook(dot)com

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

On Jan 4, 2008, at 10:00 AM, Rich Shepard wrote:

> Maybe we have a difference in semantics that is dependent upon
> the application.

The distinction can be important, as SQL has only partially
implemented 3-valued logic (TRUE/FALSE/UNKNOWN) and treats NULL in
sometimes unexpected ways. NaN could be a useful value to distinguish
between truly unknown quantities (say, that particular machine was not
taking measurements during a particular test) and those where you've
received a value from a machine and it's NaN. But I agree, it does
depend on the application.

Michael Glaesemann
michael(dot)glaesemann(at)myyearbook(dot)com