Does PostgreSQL numeric type support infinity (and -infinity)?

Question

Following on from this question and subsequent answers: Appropriate values for -Infinity & Infinity in Postgres

and the documentation, it seems clear that the real and double precision number types support both positive and negative infinity. However, no mention is made for the numeric type, except that the range has "no limit".

Are positive and negative infinity supported for numeric types (in PostgreSQL 9.5), and if so, how does one insert such values?

EDIT (as suggested by @TimBiegeleisen): The reason this came about is because I am trying to write a numeric column from R to a database table. The column contains Inf values, but using dbWriteTable from RPostgreSQL errors with:

Error in postgresqlgetResult(new.con) : 
  RS-DBI driver: (could not Retrieve the result : 
ERROR:  invalid input syntax for type numeric: "Inf"

In my particular case I can convert Inf to NA and write these values as NULL, but this doesn't work when the column contains missing values, or the not null condition is imposed in the database. I suppose another thing to do would be to write an arbitrarily large number.

Answer 1

PostgreSQL will support numeric +inf/-inf from v14

Answer 2

NUMERIC does not support +-infinity, though it does support NaN. That's rather an unfortunate limitation. Addressing it would probably require changing the on-disk binary format of numeric which could be challenging...

craig=> SELECT NUMERIC 'NaN';
 numeric 
---------
     NaN
(1 row)

craig=> SELECT NUMERIC '-inf';
ERROR:  invalid input syntax for type numeric: "-inf"
LINE 1: SELECT NUMERIC '-inf';
                       ^
craig=> SELECT NUMERIC '+inf';
ERROR:  invalid input syntax for type numeric: "+inf"
LINE 1: SELECT NUMERIC '+inf';

craig=> SELECT NUMERIC '+infinity';
ERROR:  invalid input syntax for type numeric: "+infinity"
LINE 1: SELECT NUMERIC '+infinity';
                       ^
craig=> SELECT NUMERIC 'infinity';
ERROR:  invalid input syntax for type numeric: "infinity"
LINE 1: SELECT NUMERIC 'infinity';
                       ^
craig=> SELECT NUMERIC '-infinity';
ERROR:  invalid input syntax for type numeric: "-infinity"
LINE 1: SELECT NUMERIC '-infinity';

Answer 3

Floating point types have the following special values:

Infinity
-Infinity
NaN

This is the case as far back as v8.2, and is still current.

(Ref: https://www.postgresql.org/docs/9.6/static/datatype-numeric.html)

---

EDIT: I can see the problem you are trying to solve. Coming from a maths background, I can see why you might want for example integer values including positive and negative infinity, and NaN (for asymptotes, limits, etc). Unfortunately PostgreSQL doesn't have those values available for integers or decimal types. You could mock it though, by having two columns:

"myNumeric" NUMERIC,
"myNumericSpecialInformation" REAL

Then in the column myNumericSpecialInformation either store NULL if there is no special information, or store (as per requirement) Infinity, -Infinity or NaN, and handle population and management of these values in your application business logic.