Node-postgres: Update query doesn't work in node-postgres, but works in Postgresql

Created on 7 Aug 2018  路  2Comments  路  Source: brianc/node-postgres

I have this kind of query:

UPDATE stores SET location = point('((1,1),(1,2),(2,2),(2,1),(1,1))'::POLYGON) WHERE id = 'store_id'

which works fine when tested on Postgresql directly, but running it from node it spits: invalid input syntax for type point: "point('((1,1),(1,2),(2,2),(2,1),(1,1))'::POLYGON)"

I'm calling pool.query(text, values); where:
text = UPDATE stores SET location=$1 WHERE id = $2
values = ['point(\'((1,1),(1,2),(2,2),(2,1),(1,1))\'::POLYGON)', 'store_id']

Any ideas what went wrong here?

Postgresql 9.6.1
Nodejs v8.9.4
pg 6.2.3

question

Most helpful comment

The point(...) function call and ::POLYGON cast shouldn't be part of the parameter value. They're part of the command.

So something like this:

const sql = 'UPDATE stores SET location = POINT($1::POLYGON) WHERE id = $2';
const params = ['((1,1),(1,2),(2,2),(2,1),(1,1))', 'store_id'];
return pool.query(sql, params);

All 2 comments

The point(...) function call and ::POLYGON cast shouldn't be part of the parameter value. They're part of the command.

So something like this:

const sql = 'UPDATE stores SET location = POINT($1::POLYGON) WHERE id = $2';
const params = ['((1,1),(1,2),(2,2),(2,1),(1,1))', 'store_id'];
return pool.query(sql, params);

Yes, that works, didn't think of a parameter value like that.

Was this page helpful?
0 / 5 - 0 ratings