Обсуждение: 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@svana.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.glaesemann@myyearbook.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.glaesemann@myyearbook.com