This is the code that turns all the raw text from postgres into JavaScript types for node-postgres
This module is consumed and exported from the root pg
object of node-postgres. To access it, do the following:
var types = require('pg').types
Generally what you'll want to do is override how a specific data-type is parsed and turned into a JavaScript type. By default the PostgreSQL backend server returns everything as strings. Every data type corresponds to a unique OID
within the server, and these OIDs
are sent back with the query response. So, you need to match a particluar OID
to a function you'd like to use to take the raw text input and produce a valid JavaScript object as a result. null
values are never parsed.
Let's do something I commonly like to do on projects: return 64-bit integers (int8)
as JavaScript integers. Because JavaScript doesn't have support for 64-bit integers node-postgres cannot confidently parse int8
data type results as numbers because if you have a huge number it will overflow and the result you'd get back from node-postgres would not be the result in the database. That would be a very bad thing so node-postgres just returns int8
results as strings and leaves the parsing up to you. Let's say that you know you don't and wont ever have numbers greater than int4
in your database, but you're tired of receiving results from the COUNT(*)
function as strings (because that function returns int8
). You would do this:
var types = require('pg').types
types.setTypeParser(20, function(val) {
return parseInt(val, 10)
})
boom: now you get numbers instead of strings.
Just as another example -- not saying this is a good idea -- let's say you want to return all dates from your database as moment objects. Okay, do this:
var types = require('pg').types
var moment = require('moment')
var parseFn = function(val) {
return val === null ? null : moment(val)
}
types.setTypeParser(types.builtins.TIMESTAMPTZ, parseFn)
types.setTypeParser(types.builtins.TIMESTAMP, parseFn)
note: I've never done that with my dates, and I'm not 100% sure moment can parse all the date strings returned from postgres. It's just an example!
If you're thinking "gee, this seems pretty handy, but how can I get a list of all the OIDs in the database and what they correspond to?!?!?!" worry not:
$ psql -c "select typname, oid, typarray from pg_type order by oid"
If you want to find out the OID of a specific type:
$ psql -c "select typname, oid, typarray from pg_type where typname = 'daterange' order by oid"
:smile:
The MIT License (MIT)
Copyright (c) 2014 Brian M. Carlson
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.