Numeric fields with NaN being converted to null or "NaN" (String)

[emartinez-usgs]

I have a database with a numeric array which sometimes contains NaN values. When querying these data, the resulting rows instead contain array of null values.

PostgreSQL Command Line

postgres=# \d data
                           Table "deterministic.data"
  Column   |   Type    | Collation | Nullable |             Default              
-----------+-----------+-----------+----------+----------------------------------
 id        | integer   |           | not null | nextval('data_id_seq'::regclass)
 region_id | integer   |           | not null | 
 latitude  | numeric   |           | not null | 
 longitude | numeric   |           | not null | 
 pgad      | numeric   |           |          | 
 sad       | numeric[] |           |          | 

postgres=# select * from data limit 1;
    id    | region_id | latitude | longitude | pgad |                                            sad                                            
----------+-----------+----------+-----------+------+-------------------------------------------------------------------------------------------
 35747804 |        14 |       50 |     -76.1 |  NaN | {NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN}

Node (node-postgres)

'use strict';

const { Client } = require('pg').native;
const DB_CONFIG = require('./db-config.js');

const db = new Client(DB_CONFIG);

db.connect(err => {
  if (err) {
    process.stderr.write(err + '\n');
  } else {
    db.query('SELECT * FROM deterministic.data LIMIT 1', (err, result) => {
      if (err) {
        process.stderr.write(err + '\n');
      } else {
        process.stdout.write(JSON.stringify(result.rows[0], null, 2) + '\n');
        process.exit(0);
      }
    });
  }
});

Node Output

$ node index.js 
{
  "id": 35747804,
  "region_id": 14,
  "latitude": "50",
  "longitude": "-76.1",
  "pgad": "NaN",
  "sad": [
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null
  ]
}

[sehrope]

I think there's a bug here but it's not the NaN handling. The parsed value that is being returned back does have a NaN for those entries. You can check that with isNan(...). It's JSON.stringify(...) that's converting the NaN to null:

> console.log('%s', JSON.stringify(NaN));
null

The real bug here is that numeric[] should not be parsed as a float at all as it could lose precision. A non-array numeric is not parsed, it is returned as a string so I'd expect a numeric[] to do the same for each entry of the array. But it's registered in pg-types as being parsed as a float array: https://github.com/brianc/node-pg-types/blob/master/lib/textParsers.js#L189

For consistency this should be fixed but it's definitely a breaking change for anyone relying on a numeric[] being parsed to an array of Numbers.

[emartinez-usgs]

Thanks @sehrope! You are correct about JSON.stringify. With your suggestion, would the returned value then be an array of "NaN" (String)? Also, I don't see scalar numeric fields registered? Is just text (pass-thru) the default for any non-registered types?

I do see the parsing behavior you described when querying for non-NaN data. We don't lose precision however (at least in this case; I agree it is possible).

PostgreSQL Client

postgres=# select sad from deterministic.data where pgad != double precision 'NaN' limit 1;
                                                                                                                                  sad                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {0.269339838,0.273297358,0.268610279,0.26392016,0.299581306,0.396153779,0.479797631,0.581924997,0.628567509,0.668119737,0.721384076,0.770684292,0.770902487,0.675141585,0.588161603,0.462178222,0.3673307,0.248947902,0.183883137,0.143277669,0.093694271,0.064791502}
(1 row)

Node (node-postgres)

{
  "id": 35935590,
  "region_id": 15,
  "latitude": "32.2",
  "longitude": "-114.65",
  "pgad": "0.269339691710655",
  "sad": [
    0.269339838,
    0.273297358,
    0.268610279,
    0.26392016,
    0.299581306,
    0.396153779,
    0.479797631,
    0.581924997,
    0.628567509,
    0.668119737,
    0.721384076,
    0.770684292,
    0.770902487,
    0.675141585,
    0.588161603,
    0.462178222,
    0.3673307,
    0.248947902,
    0.183883137,
    0.143277669,
    0.093694271,
    0.064791502
  ]
}

[sehrope]

Yes the text representation is the default for any type that is not explicitly registered.

[charmander]

This is fixed in pg-types ≥ 3.0.0, but it looks like there are enough potential breaking changes that pg 8.x will never have it.