From a57d312a7706321d850faa048a562a0c0c01b835 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 22 Jul 2020 19:19:44 -0400 Subject: Support infinity and -infinity in the numeric data type. Add infinities that behave the same as they do in the floating-point data types. Aside from any intrinsic usefulness these may have, this closes an important gap in our ability to convert floating values to numeric and/or replace float-based APIs with numeric. The new values are represented by bit patterns that were formerly not used (although old code probably would take them for NaNs). So there shouldn't be any pg_upgrade hazard. Patch by me, reviewed by Dean Rasheed and Andrew Gierth Discussion: https://postgr.es/m/606717.1591924582@sss.pgh.pa.us --- doc/src/sgml/datatype.sgml | 75 ++++++++++++++++++++++++++++++++++++---------- 1 file changed, 59 insertions(+), 16 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 7027758d28d..50e370cae44 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -554,9 +554,9 @@ NUMERIC(precision) NUMERIC - without any precision or scale creates a column in which numeric - values of any precision and scale can be stored, up to the - implementation limit on precision. A column of this kind will + without any precision or scale creates an unconstrained + numeric column in which numeric values of any length can be + stored, up to the implementation limits. A column of this kind will not coerce input values to any particular scale, whereas numeric columns with a declared scale will coerce input values to that scale. (The SQL standard @@ -568,10 +568,10 @@ NUMERIC - The maximum allowed precision when explicitly specified in the - type declaration is 1000; NUMERIC without a specified - precision is subject to the limits described in . + The maximum precision that can be explicitly specified in + a NUMERIC type declaration is 1000. An + unconstrained NUMERIC column is subject to the limits + described in . @@ -593,6 +593,11 @@ NUMERIC plus three to eight bytes overhead. + + infinity + numeric (data type) + + NaN not a number @@ -604,13 +609,44 @@ NUMERIC - 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. + In addition to ordinary numeric values, the numeric type + has several special values: + +Infinity +-Infinity +NaN + + These are adapted from the IEEE 754 standard, and represent + infinity, negative infinity, and + not-a-number, respectively. When writing these values + as constants in an SQL command, you must put quotes around them, + for example UPDATE table SET x = '-Infinity'. + On input, these strings are recognized in a case-insensitive manner. + The infinity values can alternatively be spelled inf + and -inf. + + + + The infinity values behave as per mathematical expectations. For + example, Infinity plus any finite value equals + Infinity, as does Infinity + plus Infinity; but Infinity + minus Infinity yields NaN (not a + number), because it has no well-defined interpretation. Note that an + infinity can only be stored in an unconstrained numeric + column, because it notionally exceeds any finite precision limit. + + + + The NaN (not a number) value is used to represent + undefined calculational results. In general, any operation with + a NaN input yields another NaN. + The only exception is when the operation's other inputs are such that + the same output would be obtained if the NaN were to + be replaced by any finite or infinite numeric value; then, that output + value is used for NaN too. (An example of this + principle is that NaN raised to the zero power + yields one.) @@ -781,9 +817,14 @@ FROM generate_series(-3.5, 3.5, 1) as x; + + infinity + floating point + + not a number - double precision + floating point @@ -800,11 +841,13 @@ FROM generate_series(-3.5, 3.5, 1) as x; as constants in an SQL command, you must put quotes around them, for example UPDATE table SET x = '-Infinity'. On input, these strings are recognized in a case-insensitive manner. + The infinity values can alternatively be spelled inf + and -inf. - IEEE754 specifies that NaN should not compare equal + IEEE 754 specifies that NaN should not compare equal to any other floating-point value (including NaN). In order to allow floating-point values to be sorted and used in tree-based indexes, PostgreSQL treats -- cgit v1.2.3