Skip to content

Commit a57d312

Browse files
tglsfdctglsfdc
committed
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
1 parent 9e10898 commit a57d312

File tree

12 files changed

+2251
-380
lines changed

12 files changed

+2251
-380
lines changed

‎contrib/jsonb_plperl/jsonb_plperl.c

Lines changed: 2 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -227,10 +227,8 @@ SV_to_JsonbValue(SV *in, JsonbParseState **jsonb_state, bool is_elem)
227227
/*
228228
* jsonb doesn't allow infinity or NaN (per JSON
229229
* specification), but the numeric type that is used for the
230-
* storage accepts NaN, so we have to prevent it here
231-
* explicitly. We don't really have to check for isinf()
232-
* here, as numeric doesn't allow it and it would be caught
233-
* later, but it makes for a nicer error message.
230+
* storage accepts those, so we have to reject them here
231+
* explicitly.
234232
*/
235233
if (isinf(nval))
236234
ereport(ERROR,

‎contrib/jsonb_plpython/jsonb_plpython.c

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -387,14 +387,17 @@ PLyNumber_ToJsonbValue(PyObject *obj, JsonbValue *jbvNum)
387387
pfree(str);
388388

389389
/*
390-
* jsonb doesn't allow NaN (per JSON specification), so we have to prevent
391-
* it here explicitly. (Infinity is also not allowed in jsonb, but
392-
* numeric_in above already catches that.)
390+
* jsonb doesn't allow NaN or infinity (per JSON specification), so we
391+
* have to reject those here explicitly.
393392
*/
394393
if (numeric_is_nan(num))
395394
ereport(ERROR,
396395
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
397396
errmsg("cannot convert NaN to jsonb")));
397+
if (numeric_is_inf(num))
398+
ereport(ERROR,
399+
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
400+
errmsg("cannot convert infinity to jsonb")));
398401

399402
jbvNum->type = jbvNumeric;
400403
jbvNum->val.numeric = num;

‎doc/src/sgml/datatype.sgml

Lines changed: 59 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -554,9 +554,9 @@ NUMERIC(<replaceable>precision</replaceable>)
554554
<programlisting>
555555
NUMERIC
556556
</programlisting>
557-
without any precision or scale creates a column in which numeric
558-
values of any precision and scale can be stored, up to the
559-
implementation limit on precision. A column of this kind will
557+
without any precision or scale creates an <quote>unconstrained
558+
numeric</quote> column in which numeric values of any length can be
559+
stored, up to the implementation limits. A column of this kind will
560560
not coerce input values to any particular scale, whereas
561561
<type>numeric</type> columns with a declared scale will coerce
562562
input values to that scale. (The <acronym>SQL</acronym> standard
@@ -568,10 +568,10 @@ NUMERIC
568568

569569
<note>
570570
<para>
571-
The maximum allowed precision when explicitly specified in the
572-
type declaration is 1000; <type>NUMERIC</type> without a specified
573-
precision is subject to the limits described in <xref
574-
linkend="datatype-numeric-table"/>.
571+
The maximum precision that can be explicitly specified in
572+
a <type>NUMERIC</type> type declaration is 1000. An
573+
unconstrained <type>NUMERIC</type> column is subject to the limits
574+
described in <xref linkend="datatype-numeric-table"/>.
575575
</para>
576576
</note>
577577

@@ -593,6 +593,11 @@ NUMERIC
593593
plus three to eight bytes overhead.
594594
</para>
595595

596+
<indexterm>
597+
<primary>infinity</primary>
598+
<secondary>numeric (data type)</secondary>
599+
</indexterm>
600+
596601
<indexterm>
597602
<primary>NaN</primary>
598603
<see>not a number</see>
@@ -604,13 +609,44 @@ NUMERIC
604609
</indexterm>
605610

606611
<para>
607-
In addition to ordinary numeric values, the <type>numeric</type>
608-
type allows the special value <literal>NaN</literal>, meaning
609-
<quote>not-a-number</quote>. Any operation on <literal>NaN</literal>
610-
yields another <literal>NaN</literal>. When writing this value
611-
as a constant in an SQL command, you must put quotes around it,
612-
for example <literal>UPDATE table SET x = 'NaN'</literal>. On input,
613-
the string <literal>NaN</literal> is recognized in a case-insensitive manner.
612+
In addition to ordinary numeric values, the <type>numeric</type> type
613+
has several special values:
614+
<literallayout>
615+
<literal>Infinity</literal>
616+
<literal>-Infinity</literal>
617+
<literal>NaN</literal>
618+
</literallayout>
619+
These are adapted from the IEEE 754 standard, and represent
620+
<quote>infinity</quote>, <quote>negative infinity</quote>, and
621+
<quote>not-a-number</quote>, respectively. When writing these values
622+
as constants in an SQL command, you must put quotes around them,
623+
for example <literal>UPDATE table SET x = '-Infinity'</literal>.
624+
On input, these strings are recognized in a case-insensitive manner.
625+
The infinity values can alternatively be spelled <literal>inf</literal>
626+
and <literal>-inf</literal>.
627+
</para>
628+
629+
<para>
630+
The infinity values behave as per mathematical expectations. For
631+
example, <literal>Infinity</literal> plus any finite value equals
632+
<literal>Infinity</literal>, as does <literal>Infinity</literal>
633+
plus <literal>Infinity</literal>; but <literal>Infinity</literal>
634+
minus <literal>Infinity</literal> yields <literal>NaN</literal> (not a
635+
number), because it has no well-defined interpretation. Note that an
636+
infinity can only be stored in an unconstrained <type>numeric</type>
637+
column, because it notionally exceeds any finite precision limit.
638+
</para>
639+
640+
<para>
641+
The <literal>NaN</literal> (not a number) value is used to represent
642+
undefined calculational results. In general, any operation with
643+
a <literal>NaN</literal> input yields another <literal>NaN</literal>.
644+
The only exception is when the operation's other inputs are such that
645+
the same output would be obtained if the <literal>NaN</literal> were to
646+
be replaced by any finite or infinite numeric value; then, that output
647+
value is used for <literal>NaN</literal> too. (An example of this
648+
principle is that <literal>NaN</literal> raised to the zero power
649+
yields one.)
614650
</para>
615651

616652
<note>
@@ -781,9 +817,14 @@ FROM generate_series(-3.5, 3.5, 1) as x;
781817
</para>
782818
</note>
783819

820+
<indexterm>
821+
<primary>infinity</primary>
822+
<secondary>floating point</secondary>
823+
</indexterm>
824+
784825
<indexterm>
785826
<primary>not a number</primary>
786-
<secondary>double precision</secondary>
827+
<secondary>floating point</secondary>
787828
</indexterm>
788829

789830
<para>
@@ -800,11 +841,13 @@ FROM generate_series(-3.5, 3.5, 1) as x;
800841
as constants in an SQL command, you must put quotes around them,
801842
for example <literal>UPDATE table SET x = '-Infinity'</literal>. On input,
802843
these strings are recognized in a case-insensitive manner.
844+
The infinity values can alternatively be spelled <literal>inf</literal>
845+
and <literal>-inf</literal>.
803846
</para>
804847

805848
<note>
806849
<para>
807-
IEEE754 specifies that <literal>NaN</literal> should not compare equal
850+
IEEE 754 specifies that <literal>NaN</literal> should not compare equal
808851
to any other floating-point value (including <literal>NaN</literal>).
809852
In order to allow floating-point values to be sorted and used
810853
in tree-based indexes, <productname>PostgreSQL</productname> treats

‎src/backend/utils/adt/formatting.c

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -6129,9 +6129,12 @@ numeric_to_char(PG_FUNCTION_ARGS)
61296129
/*
61306130
* numeric_out_sci() does not emit a sign for positive numbers. We
61316131
* need to add a space in this case so that positive and negative
6132-
* numbers are aligned. We also have to do the right thing for NaN.
6132+
* numbers are aligned. Also must check for NaN/infinity cases, which
6133+
* we handle the same way as in float8_to_char.
61336134
*/
6134-
if (strcmp(orgnum, "NaN") == 0)
6135+
if (strcmp(orgnum, "NaN") == 0 ||
6136+
strcmp(orgnum, "Infinity") == 0 ||
6137+
strcmp(orgnum, "-Infinity") == 0)
61356138
{
61366139
/*
61376140
* Allow 6 characters for the leading sign, the decimal point,
@@ -6346,7 +6349,7 @@ int8_to_char(PG_FUNCTION_ARGS)
63466349
/*
63476350
* numeric_out_sci() does not emit a sign for positive numbers. We
63486351
* need to add a space in this case so that positive and negative
6349-
* numbers are aligned. We don't have to worry about NaN here.
6352+
* numbers are aligned. We don't have to worry about NaN/inf here.
63506353
*/
63516354
if (*orgnum != '-')
63526355
{

0 commit comments

Comments
 (0)