JavaScript (Postgres DB) - How to use a prepared statement with an array as parameter in the WHERE IN ( )...
I am currently using the database class from http://vitaly-t.github.io/pg-promise/Database.html and trying to implement an Update statement using a PreparedStatment on my Postgres DB while having an Array passed to the WHERE IN clause ?
const updatePreparedStatment = new PS('prepared-statement', 'UPDATE mytable SET "MESSAGE"=$1 WHERE "ID" IN ($2)', ["dummy update", ["1","2","3"]]);
javascript node.js postgresql pg-promise
edited Nov 14 at 9:43
wanttobeprofessional
91131223
asked Nov 13 at 11:44
Azakaria
112
add a comment |
I am currently using the database class from http://vitaly-t.github.io/pg-promise/Database.html and trying to implement an Update statement using a PreparedStatment on my Postgres DB while having an Array passed to the WHERE IN clause ?
const updatePreparedStatment = new PS('prepared-statement', 'UPDATE mytable SET "MESSAGE"=$1 WHERE "ID" IN ($2)', ["dummy update", ["1","2","3"]]);
javascript node.js postgresql pg-promise
edited Nov 14 at 9:43
wanttobeprofessional
91131223
asked Nov 13 at 11:44
Azakaria
112
Asking your question well increases the likelihood that you'll get help. You should post your code here, in your question, rather than posting a link to an external page. A sample of the table(s) you're querying would also be helpful. Guidelines on asking question well can be found here: stackoverflow.com/help/how-to-ask
– Matt Morgan
Nov 13 at 12:10
By very definition of Prepared Statements, variable formatting in them occurs on the server-side, which means you cannot make use of the powerful query-formatting engine that's insidepg-promise, limiting yourself to just basic$1, $2,...variables supported by PostgreSQL itself, and so you'd have to format thatWHERE INpart all by yourself. You should ask yourself first, whether you really need Prepared Statements to begin with.
– vitaly-t
Nov 13 at 13:41
add a comment |
I am currently using the database class from http://vitaly-t.github.io/pg-promise/Database.html and trying to implement an Update statement using a PreparedStatment on my Postgres DB while having an Array passed to the WHERE IN clause ?
const updatePreparedStatment = new PS('prepared-statement', 'UPDATE mytable SET "MESSAGE"=$1 WHERE "ID" IN ($2)', ["dummy update", ["1","2","3"]]);
javascript node.js postgresql pg-promise
edited Nov 14 at 9:43
wanttobeprofessional
91131223
asked Nov 13 at 11:44
Azakaria
112
I am currently using the database class from http://vitaly-t.github.io/pg-promise/Database.html and trying to implement an Update statement using a PreparedStatment on my Postgres DB while having an Array passed to the WHERE IN clause ?
const updatePreparedStatment = new PS('prepared-statement', 'UPDATE mytable SET "MESSAGE"=$1 WHERE "ID" IN ($2)', ["dummy update", ["1","2","3"]]);
javascript node.js postgresql pg-promise
javascript node.js postgresql pg-promise
edited Nov 14 at 9:43
wanttobeprofessional
91131223
asked Nov 13 at 11:44
Azakaria
112
edited Nov 14 at 9:43
wanttobeprofessional
91131223
asked Nov 13 at 11:44
Azakaria
112
edited Nov 14 at 9:43
wanttobeprofessional
91131223
edited Nov 14 at 9:43
wanttobeprofessional
91131223
edited Nov 14 at 9:43
wanttobeprofessional
91131223
91131223
asked Nov 13 at 11:44
Azakaria
112
asked Nov 13 at 11:44
Azakaria
112
asked Nov 13 at 11:44
Azakaria
112
112
Asking your question well increases the likelihood that you'll get help. You should post your code here, in your question, rather than posting a link to an external page. A sample of the table(s) you're querying would also be helpful. Guidelines on asking question well can be found here: stackoverflow.com/help/how-to-ask
– Matt Morgan
Nov 13 at 12:10
By very definition of Prepared Statements, variable formatting in them occurs on the server-side, which means you cannot make use of the powerful query-formatting engine that's insidepg-promise, limiting yourself to just basic$1, $2,...variables supported by PostgreSQL itself, and so you'd have to format thatWHERE INpart all by yourself. You should ask yourself first, whether you really need Prepared Statements to begin with.
– vitaly-t
Nov 13 at 13:41
add a comment |
Asking your question well increases the likelihood that you'll get help. You should post your code here, in your question, rather than posting a link to an external page. A sample of the table(s) you're querying would also be helpful. Guidelines on asking question well can be found here: stackoverflow.com/help/how-to-ask
– Matt Morgan
Nov 13 at 12:10
By very definition of Prepared Statements, variable formatting in them occurs on the server-side, which means you cannot make use of the powerful query-formatting engine that's insidepg-promise, limiting yourself to just basic$1, $2,...variables supported by PostgreSQL itself, and so you'd have to format thatWHERE INpart all by yourself. You should ask yourself first, whether you really need Prepared Statements to begin with.
– vitaly-t
Nov 13 at 13:41
Asking your question well increases the likelihood that you'll get help. You should post your code here, in your question, rather than posting a link to an external page. A sample of the table(s) you're querying would also be helpful. Guidelines on asking question well can be found here: stackoverflow.com/help/how-to-ask
– Matt Morgan
Nov 13 at 12:10
Asking your question well increases the likelihood that you'll get help. You should post your code here, in your question, rather than posting a link to an external page. A sample of the table(s) you're querying would also be helpful. Guidelines on asking question well can be found here: stackoverflow.com/help/how-to-ask
– Matt Morgan
Nov 13 at 12:10
By very definition of Prepared Statements, variable formatting in them occurs on the server-side, which means you cannot make use of the powerful query-formatting engine that's inside
pg-promise, limiting yourself to just basic $1, $2,... variables supported by PostgreSQL itself, and so you'd have to format that WHERE IN part all by yourself. You should ask yourself first, whether you really need Prepared Statements to begin with.– vitaly-t
Nov 13 at 13:41
By very definition of Prepared Statements, variable formatting in them occurs on the server-side, which means you cannot make use of the powerful query-formatting engine that's inside
pg-promise, limiting yourself to just basic $1, $2,... variables supported by PostgreSQL itself, and so you'd have to format that WHERE IN part all by yourself. You should ask yourself first, whether you really need Prepared Statements to begin with.– vitaly-t
Nov 13 at 13:41
add a comment |
1 Answer
1
active
oldest
votes
It is described in the FAQ of node-postgres https://github.com/brianc/node-postgres/wiki/FAQ#11-how-do-i-build-a-where-foo-in--query-to-find-rows-matching-an-array-of-values
How do I build a WHERE foo IN (...) query to find rows matching an array of values?
node-postgres supports mapping simple JavaScript arrays to PostgreSQL arrays, so in most cases you can just pass it like any other parameter.
client.query("SELECT * FROM stooges WHERE name = ANY ($1)", [ ['larry', 'curly', 'moe'] ], ...);
Note that = ANY is another way to write IN (...), but unlike IN (...) it will work how you'd expect when you pass an array as a query parameter.
If you know the length of the array in advance you can flatten it to an IN list:
// passing a flat array of values will work:
client.query("SELECT * FROM stooges WHERE name IN ($1, $2, $3)", ['larry', 'curly', 'moe'], ...);
... but there's little benefit when = ANY works with a JavaScript array.
If you're on an old version of node-postgres or you need to create more complex PostgreSQL arrays (arrays of composite types, etc) that node-postgres isn't coping with, you can generate an array literal with dynamic SQL, but be extremely careful of SQL injection when doing this. The following approach is safe because it generates a query string with query parameters and a flattened parameter list, so you're still using the driver's support for parameterised queries ("prepared statements") to protect against SQL injection:
var stooge_names = ['larry', 'curly', 'moe'];
var offset = 1;
var placeholders = stooge_names.map(function(name,i) {
return '$'+(i+offset);
}).join(',');
client.query("SELECT * FROM stooges WHERE name IN ("+placeholders+")", stooge_names, ...);
Hope that helps since google fails to find this
answered Nov 14 at 8:40
BlackC0de
11
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53280333%2fjavascript-postgres-db-how-to-use-a-prepared-statement-with-an-array-as-para%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
It is described in the FAQ of node-postgres https://github.com/brianc/node-postgres/wiki/FAQ#11-how-do-i-build-a-where-foo-in--query-to-find-rows-matching-an-array-of-values
How do I build a WHERE foo IN (...) query to find rows matching an array of values?
node-postgres supports mapping simple JavaScript arrays to PostgreSQL arrays, so in most cases you can just pass it like any other parameter.
client.query("SELECT * FROM stooges WHERE name = ANY ($1)", [ ['larry', 'curly', 'moe'] ], ...);
Note that = ANY is another way to write IN (...), but unlike IN (...) it will work how you'd expect when you pass an array as a query parameter.
If you know the length of the array in advance you can flatten it to an IN list:
// passing a flat array of values will work:
client.query("SELECT * FROM stooges WHERE name IN ($1, $2, $3)", ['larry', 'curly', 'moe'], ...);
... but there's little benefit when = ANY works with a JavaScript array.
If you're on an old version of node-postgres or you need to create more complex PostgreSQL arrays (arrays of composite types, etc) that node-postgres isn't coping with, you can generate an array literal with dynamic SQL, but be extremely careful of SQL injection when doing this. The following approach is safe because it generates a query string with query parameters and a flattened parameter list, so you're still using the driver's support for parameterised queries ("prepared statements") to protect against SQL injection:
var stooge_names = ['larry', 'curly', 'moe'];
var offset = 1;
var placeholders = stooge_names.map(function(name,i) {
return '$'+(i+offset);
}).join(',');
client.query("SELECT * FROM stooges WHERE name IN ("+placeholders+")", stooge_names, ...);
Hope that helps since google fails to find this
answered Nov 14 at 8:40
BlackC0de
11
add a comment |
It is described in the FAQ of node-postgres https://github.com/brianc/node-postgres/wiki/FAQ#11-how-do-i-build-a-where-foo-in--query-to-find-rows-matching-an-array-of-values
How do I build a WHERE foo IN (...) query to find rows matching an array of values?
node-postgres supports mapping simple JavaScript arrays to PostgreSQL arrays, so in most cases you can just pass it like any other parameter.
client.query("SELECT * FROM stooges WHERE name = ANY ($1)", [ ['larry', 'curly', 'moe'] ], ...);
Note that = ANY is another way to write IN (...), but unlike IN (...) it will work how you'd expect when you pass an array as a query parameter.
If you know the length of the array in advance you can flatten it to an IN list:
// passing a flat array of values will work:
client.query("SELECT * FROM stooges WHERE name IN ($1, $2, $3)", ['larry', 'curly', 'moe'], ...);
... but there's little benefit when = ANY works with a JavaScript array.
If you're on an old version of node-postgres or you need to create more complex PostgreSQL arrays (arrays of composite types, etc) that node-postgres isn't coping with, you can generate an array literal with dynamic SQL, but be extremely careful of SQL injection when doing this. The following approach is safe because it generates a query string with query parameters and a flattened parameter list, so you're still using the driver's support for parameterised queries ("prepared statements") to protect against SQL injection:
var stooge_names = ['larry', 'curly', 'moe'];
var offset = 1;
var placeholders = stooge_names.map(function(name,i) {
return '$'+(i+offset);
}).join(',');
client.query("SELECT * FROM stooges WHERE name IN ("+placeholders+")", stooge_names, ...);
Hope that helps since google fails to find this
answered Nov 14 at 8:40
BlackC0de
11
add a comment |
It is described in the FAQ of node-postgres https://github.com/brianc/node-postgres/wiki/FAQ#11-how-do-i-build-a-where-foo-in--query-to-find-rows-matching-an-array-of-values
How do I build a WHERE foo IN (...) query to find rows matching an array of values?
node-postgres supports mapping simple JavaScript arrays to PostgreSQL arrays, so in most cases you can just pass it like any other parameter.
client.query("SELECT * FROM stooges WHERE name = ANY ($1)", [ ['larry', 'curly', 'moe'] ], ...);
Note that = ANY is another way to write IN (...), but unlike IN (...) it will work how you'd expect when you pass an array as a query parameter.
If you know the length of the array in advance you can flatten it to an IN list:
// passing a flat array of values will work:
client.query("SELECT * FROM stooges WHERE name IN ($1, $2, $3)", ['larry', 'curly', 'moe'], ...);
... but there's little benefit when = ANY works with a JavaScript array.
If you're on an old version of node-postgres or you need to create more complex PostgreSQL arrays (arrays of composite types, etc) that node-postgres isn't coping with, you can generate an array literal with dynamic SQL, but be extremely careful of SQL injection when doing this. The following approach is safe because it generates a query string with query parameters and a flattened parameter list, so you're still using the driver's support for parameterised queries ("prepared statements") to protect against SQL injection:
var stooge_names = ['larry', 'curly', 'moe'];
var offset = 1;
var placeholders = stooge_names.map(function(name,i) {
return '$'+(i+offset);
}).join(',');
client.query("SELECT * FROM stooges WHERE name IN ("+placeholders+")", stooge_names, ...);
Hope that helps since google fails to find this
answered Nov 14 at 8:40
BlackC0de
11
It is described in the FAQ of node-postgres https://github.com/brianc/node-postgres/wiki/FAQ#11-how-do-i-build-a-where-foo-in--query-to-find-rows-matching-an-array-of-values
How do I build a WHERE foo IN (...) query to find rows matching an array of values?
node-postgres supports mapping simple JavaScript arrays to PostgreSQL arrays, so in most cases you can just pass it like any other parameter.
client.query("SELECT * FROM stooges WHERE name = ANY ($1)", [ ['larry', 'curly', 'moe'] ], ...);
Note that = ANY is another way to write IN (...), but unlike IN (...) it will work how you'd expect when you pass an array as a query parameter.
If you know the length of the array in advance you can flatten it to an IN list:
// passing a flat array of values will work:
client.query("SELECT * FROM stooges WHERE name IN ($1, $2, $3)", ['larry', 'curly', 'moe'], ...);
... but there's little benefit when = ANY works with a JavaScript array.
If you're on an old version of node-postgres or you need to create more complex PostgreSQL arrays (arrays of composite types, etc) that node-postgres isn't coping with, you can generate an array literal with dynamic SQL, but be extremely careful of SQL injection when doing this. The following approach is safe because it generates a query string with query parameters and a flattened parameter list, so you're still using the driver's support for parameterised queries ("prepared statements") to protect against SQL injection:
var stooge_names = ['larry', 'curly', 'moe'];
var offset = 1;
var placeholders = stooge_names.map(function(name,i) {
return '$'+(i+offset);
}).join(',');
client.query("SELECT * FROM stooges WHERE name IN ("+placeholders+")", stooge_names, ...);
Hope that helps since google fails to find this
answered Nov 14 at 8:40
BlackC0de
11
answered Nov 14 at 8:40
BlackC0de
11
answered Nov 14 at 8:40
BlackC0de
11
answered Nov 14 at 8:40
BlackC0de
11
11
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53280333%2fjavascript-postgres-db-how-to-use-a-prepared-statement-with-an-array-as-para%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Asking your question well increases the likelihood that you'll get help. You should post your code here, in your question, rather than posting a link to an external page. A sample of the table(s) you're querying would also be helpful. Guidelines on asking question well can be found here: stackoverflow.com/help/how-to-ask
– Matt Morgan
Nov 13 at 12:10
By very definition of Prepared Statements, variable formatting in them occurs on the server-side, which means you cannot make use of the powerful query-formatting engine that's inside
pg-promise, limiting yourself to just basic$1, $2,...variables supported by PostgreSQL itself, and so you'd have to format thatWHERE INpart all by yourself. You should ask yourself first, whether you really need Prepared Statements to begin with.– vitaly-t
Nov 13 at 13:41