CODEWITHSUNDEEP
node.jspostgresql
DonutGaz
DonutGaz

Reputation: 1542

Parameterized query using node-postgres isn't passed between quotes

I'm using the npm module pg with Node.js, and have the following query:

query = "SELECT * FROM territories WHERE '($1, -122.26), (47.57, -122.36)'::box \ 
         @> point(nwlat, nwlng) ORDER BY nwlat, nwlng;"
client.query(query, [lat+.05], callback);

When I run this, I get the following error:

invalid input syntax for type box: "($1, -122.26), (47.57, -122.36)"

But when I replace $1 with a decimal literal, like 47.67, it executes normally. What am I doing incorrectly?

Upvotes: 0

Views: 1213

Answers (2)

vitaly-t
vitaly-t

Reputation: 25820

Extending on the answer by @mu is too short

With pg-promise query formatting you would get exactly what you expect ;)

Upvotes: 0

mu is too short
mu is too short

Reputation: 434585

Your problem is that this:

'$1'

doesn't have a placeholder in it, it is a string literal that just happens to contain some characters that look like a numbered placeholder. So this:

'($1, -122.26), (47.57, -122.36)'

doesn't have a placeholder either, that's just a string literal that happens to contain the characters $ and 1. Consider the difference between this:

let x = 6;
let y = 'x'; // String that contains the name of a variable.

and this:

let x = 6;
let y = x;  // The actual variable itself.

in JavaScript, same idea.

You can build your box string using string concatenation:

WHERE ('(' || $1 || ', -122.26), (47.57, -122.36)')::box

but that's not very pretty. A cleaner solution would be to bypass strings and casting altogether by using the point and box functions:

WHERE box(point($1, -122.26), point(47.57, -122.36))

Upvotes: 1

Related Questions