"malformed range literal" using @> (contains) to test a scalar Date against date or timestamp ranges

[dmfay]

CREATE TABLE ranges (
  id SERIAL NOT NULL PRIMARY KEY,
  duration TSTZRANGE
);

const {Pool} = require('pg');
const pool = new Pool();

(async () => {
  const query = 'SELECT * FROM ranges WHERE duration @> $1';

  const result = await pool.query(query, [new Date()]);

  console.log(result.length);
})();

2020-05-14 18:03:41.945 EDT [34129] ERROR:  malformed range literal: "2020-05-14T18:03:41.935-04:00"
2020-05-14 18:03:41.945 EDT [34129] DETAIL:  Missing left parenthesis or bracket.
2020-05-14 18:03:41.945 EDT [34129] STATEMENT:  SELECT * FROM ranges WHERE duration @> $1

@> can test both range and scalar values, and strings are assumed to be the former (e.g. [2020-05-14T18:03:41.935-04:00,)). JavaScript Dates are stringified in prepareValue, so Postgres takes the argument here to be a candidate subrange and fails because it isn't.

I don't know what other recourse is possible, but casting the parameter ($1::TIMESTAMPTZ) does work at least.

[boromisp]

This library (and probably most others as well) is using the text format for the parameters:

const { Client } = require('pg');
const client = new Client();
client.connect();

const query = 'SELECT $1 AS a, $2 AS b, $3 AS c, $4 AS d';

client.query(query,  [1, '2', [3], new Date(4)])
  .then(result => console.log(result.rows[0]))
  .finally(() => client.end());

// result:
{ a: '1', b: '2', c: '{"3"}', d: '1970-01-01T01:00:00.004+01:00' }

If the implicit conversion is wrong in your case, the only solution is to explicitly cast the input.

Do you have some constraint that makes it difficult to write the ::timestamptz cast into the query?

[dmfay]

Not in this instance, fortunately!

[boromisp]

There is actually a way to send type information even in text mode but it doesn't seem to be supported by the library.

POC:

const { Client, Connection } = require('pg');
const { builtins: OID } = require('pg-types');

(async () => {
  const client = new Client();

  client.connection.parse = function ({text, name}) {
    return Connection.prototype.parse.call(this, {text, name, types: [OID.TIMESTAMPTZ]});
  };

  await client.connect();

  try {
    const query = 'SELECT * FROM ranges WHERE duration @> $1';
    const values = [new Date()];

    const result = await client.query(query, values);

    console.log(result.rows);
    // []

    delete client.connection.parse;

    await client.query(query, values);
    // error: malformed range literal: "2020-05-15T...
  } finally {
    client.end();
  }
})();

I don't think the JavaScript -> Postgres type deduction could generally be automated, but maybe there are a few special cases (Date -> timestamptz) where it could be useful.

If the types parameter would be exposed by this library, maybe higher level libraries could do something interesting with it.