NaN behavior
Started by Neil Conwayabout 19 years ago3 messages
postgres=# select 'NaN'::numeric = 'NaN'::numeric,
'NaN'::float8 = 'NaN'::float8;
?column? | ?column?
----------+----------
t | t
(1 row)
This behavior is inconsistent with most people's notion of "NaN" -- in
particular, it is inconsistent with IEEE754. I can understand why
Postgres behaves this way, and we probably can't easily change it (if we
want to continue indexing NaN values, that is), but I think it should at
least be discussed in the documentation.
Comments? I'll write up a doc patch, barring any objections.
-Neil
Re: [HACKERS] NaN behavior
On Thu, 2007-01-11 at 21:04 -0500, Neil Conway wrote:
Comments? I'll write up a doc patch, barring any objections.
I'll apply the attached doc patch to CVS tomorrow, barring any
objections.
-Neil
Attachments:
nan_datatype_note-1.patchtext/x-patch; charset=us-ascii; name=nan_datatype_note-1.patchDownload
Index: doc/src/sgml/datatype.sgml
===================================================================
RCS file: /home/neilc/postgres/cvs_root/pgsql/doc/src/sgml/datatype.sgml,v
retrieving revision 1.183
diff -c -p -r1.183 datatype.sgml
*** doc/src/sgml/datatype.sgml 22 Dec 2006 22:09:31 -0000 1.183
--- doc/src/sgml/datatype.sgml 12 Jan 2007 21:52:22 -0000
***************
*** 438,447 ****
<sect2 id="datatype-numeric-decimal">
<title>Arbitrary Precision Numbers</title>
! <indexterm zone="datatype-numeric-decimal">
<primary>numeric (data type)</primary>
</indexterm>
<indexterm>
<primary>decimal</primary>
<see>numeric</see>
--- 438,451 ----
<sect2 id="datatype-numeric-decimal">
<title>Arbitrary Precision Numbers</title>
! <indexterm>
<primary>numeric (data type)</primary>
</indexterm>
+ <indexterm>
+ <primary>arbitrary precision numbers</primary>
+ </indexterm>
+
<indexterm>
<primary>decimal</primary>
<see>numeric</see>
*************** NUMERIC
*** 515,520 ****
--- 519,529 ----
plus eight bytes overhead.
</para>
+ <indexterm>
+ <primary>not a number</primary>
+ <secondary>numeric (data type)</secondary>
+ </indexterm>
+
<para>
In addition to ordinary numeric values, the <type>numeric</type>
type allows the special value <literal>NaN</>, meaning
*************** NUMERIC
*** 525,530 ****
--- 534,551 ----
the string <literal>NaN</> is recognized in a case-insensitive manner.
</para>
+ <note>
+ <para>
+ In most implementations of the <quote>not-a-number</> concept,
+ <literal>NaN</> is not considered equal to any other numeric
+ value (including <literal>NaN</>). In order to allow
+ <type>numeric</> values to be sorted and used in tree-based
+ indexes, <productname>PostgreSQL</> treats <literal>NaN</>
+ values as equal, and greater than all non-<literal>NaN</>
+ values.
+ </para>
+ </note>
+
<para>
The types <type>decimal</type> and <type>numeric</type> are
equivalent. Both types are part of the <acronym>SQL</acronym>
*************** NUMERIC
*** 613,618 ****
--- 634,644 ----
from zero will cause an underflow error.
</para>
+ <indexterm>
+ <primary>not a number</primary>
+ <secondary>double precision</secondary>
+ </indexterm>
+
<para>
In addition to ordinary numeric values, the floating-point types
have several special values:
*************** NUMERIC
*** 631,636 ****
--- 657,673 ----
these strings are recognized in a case-insensitive manner.
</para>
+ <note>
+ <para>
+ IEEE754 specifies that <literal>NaN</> should not compare equal
+ to any other floating-point value (including <literal>NaN</>).
+ In order to allow floating-point values to be sorted and used
+ in tree-based indexes, <productname>PostgreSQL</> treats
+ <literal>NaN</> values as equal, and greater than all
+ non-<literal>NaN</> values.
+ </para>
+ </note>
+
<para>
<productname>PostgreSQL</productname> also supports the SQL-standard
notations <type>float</type> and