Numeric fields returned as text

[aloysius-lim]

PostgreSQL returns numeric fields as text, instead of binary, so pg-types does not parse numeric fields. The type definition for numeric fields is in lib/binaryParsers.js: register(1700, parseNumeric);. However, since PostgreSQL returns numeric data as text, the parser is not invoked.

Reproducing the problem

My environment

• PostgreSQL server 9.3.6
• pg module version 4.3.0
• pg-types module version 1.7.0

SQL table

CREATE TABLE test (
  id INTEGER,
  cost NUMERIC
);
INSERT INTO test VALUES (1, 10.50);

Node.js code

var pg = require('pg');
pg.connect('postgres://user@localhost/test_db', function(err, client, done) {
  if (err) throw err;
  client.query('SELECT * FROM test;', function(err, result) {
    if (err) throw err;
    console.log(result);
    done();
    process.exit();
  });
});

Output

{ command: 'SELECT',
  rowCount: 1,
  oid: NaN,
  rows: [ { id: 1, cost: '10.50' } ],
  fields: 
   [ { name: 'id',
       tableID: 17536,
       columnID: 1,
       dataTypeID: 23,
       dataTypeSize: 4,
       dataTypeModifier: -1,
       format: 'text' },
     { name: 'cost',
       tableID: 17536,
       columnID: 2,
       dataTypeID: 1700,
       dataTypeSize: -1,
       dataTypeModifier: -1,
       format: 'text' } ],
  _parsers: [ [Function], [Function] ],
  RowCtor: [Function],
  rowAsArray: false,
  _getTypeParser: [Function] }

From the output, the cost column has format 'text' instead of 'binary', so pg-types does not parse it, and returns it as a string (rows: [ { id: 1, cost: '10.50' } ]).

Workaround

Add 2 lines to the script to set a custom parser for numeric fields:

var pg = require('pg');

// Fix for parsing of numeric fields
var types = require('pg').types
types.setTypeParser(1700, 'text', parseFloat);

pg.connect('postgres://[email protected]/test_db', function(err, client, done) {
  if (err) throw err;
  client.query('SELECT * FROM test;', function(err, result) {
    if (err) throw err;
    console.log(result);
    done();
    process.exit();
  });
});

The output is now correct:

{ command: 'SELECT',
  rowCount: 1,
  oid: NaN,
  rows: [ { id: 1, cost: 10.5 } ],
  fields: 
   [ { name: 'id',
       tableID: 17549,
       columnID: 1,
       dataTypeID: 23,
       dataTypeSize: 4,
       dataTypeModifier: -1,
       format: 'text' },
     { name: 'cost',
       tableID: 17549,
       columnID: 2,
       dataTypeID: 1700,
       dataTypeSize: -1,
       dataTypeModifier: -1,
       format: 'text' } ],
  _parsers: [ [Function], [Function: parseFloat] ],
  RowCtor: [Function],
  rowAsArray: false,
  _getTypeParser: [Function] }

Bug Fix

Add this line to lib/textParsers.js:

var init = function(register) {
  ...
  register(1700, parseFloat);
};

[mirow]

+1
Just figured this out myself :)

[bendrucker]

Numerics cannot necessarily be safely converted to a JS Number without loss of precision. If your application uses numbers that are safe you can override the parser and explained in the readme.