Pg-promise: Select returns Infinity or -Infinity as null

[hannupekka]

time_from | time_to
-----------+----------
 -infinity | infinity
(1 row)

And then:

db.oneOrNone('SELECT * FROM rules WHERE ... LIMIT 1', [...])
  .then(function(result) {
    console.log(result);
  });

gives me

time_from: null,
time_to: null

PostgreSQL 9.4.2 and [email protected]

[hannupekka]

I guess the issue is not in this projects scope - tried also with node-postgres and got same results.

If I cast those fields to text in the select, they came out correcly:

db.oneOrNone('SELECT *, time_from::text, time_to::text FROM rules WHERE ... LIMIT 1', [...])

[vitaly-t]

All server-to-client data transformations are within node-postgres, which in turn uses pg-types, which is customizable.

[vitaly-t]

I just did some testing, and I can see that for the proper float types we are getting Infinity back. Then I noticed you are using it for Date/Time types? Why? They do not need support for Infinity, they can accept NULL, which is sufficient.

[vitaly-t]

pg-types in turn uses postgres-date to parse dates. I think if you try to change that, you will be running into an unholy mess. Date/Time coversion is the most complex part of types transformation.

If you want to manipulate dates, you can use something like:

pgp.pg.types.setTypeParser(1114, function(value){
   /// transform and return the value here...
});

which would be required for:

register(1082, parseDate); // date
register(1114, parseDate); // timestamp without timezone
register(1184, parseDate); // timestamp

register(1115, parseDateArray); // timestamp without time zone[]
register(1182, parseDateArray); // _date
register(1185, parseDateArray); // timestamp with time zone[]

Anyway, since it is definitely not within the scope of this library, I am closing the issue.

[vitaly-t]

@hannupekka This just had an unexpected follow up: https://github.com/bendrucker/postgres-date/issues/3

[bendrucker]

👍 I know node-postgres development isn't that speedy these days. The beauty of npm/modularization is that we can do much quicker patch releases on the crazy parsers!

[vitaly-t]

I know node-postgres development isn't that speedy these days.

This would be an understatement. Brian C locked my account again, for the second time, after the insane amount of support I've been doing for his library. Something isn't right with this guy...

[bendrucker]

It's hard to understand each other online. I've only blocked someone once for opening a couple of issues all at once and being a bit combative with me. I ended up undoing it after a bit and forgetting about it. I know Brian personally and am more than willing to give him the benefit of the doubt here.

If you'd like, email me (listed on my profile) with any PRs or issues you're stuck on them and I can have a look. I'd rather not continue on this thread in public.