Postgresql dialect - errors with Array Functions

[im4LF]

Simple object with integer[] type of one field

var Table = sql.define({
        schema: 'tests',
        name: 'objects',
        columns: [
            'id', 
            'some_field' // type - integer[]
        ]
});

var query = Table.select().from(Table)
            .where(
                items.some_field.contains(sql.array(123, 456))
            )
        .toQuery();

Query generates:

{ 
    text: 'SELECT "tests"."objects".* FROM "tests"."objects" WHERE ("tests"."objects"."some_field" @> ARRAY[$1, $2])',
    values: [ 123, 456 ]
}

But when send query to postgres by

client.query(query.text, query.values)

Its generate type cast error:

error: operator does not exist: integer[] @> text[]

[im4LF]

Anybody?

[stephenkubovic]

I am experiencing this as well, however I think the root cause is within the pg module, specifically this issue: https://github.com/brianc/node-postgres/issues/220.

The thread suggests monkey-patching https://github.com/brianc/node-postgres/blob/715e5009dde23c0fb5b127f9b536aa6ed5245fc2/lib/utils.js#L30, I'm not sure if that's still the only solution.

[im4LF]

My solution is to try guess the type by first value:

Postgres.prototype.visitArrayCall = function(arrayCall) {
  var type = 'text';
  if (arrayCall.nodes.length) {
    var test = arrayCall.nodes[0].value();
    if ('number' === typeof test)
      type = !!(test % 1) ? 'double precision' : 'integer';
  }
  var txt = 'ARRAY[' + arrayCall.nodes.map(this.visit.bind(this)).join(', ') + ']::' + type + '[]';
  return [txt];
};