postgreSQL query only users that their id is in the array

Question

I'm using postgreSQL as my database for node.js app, I'm given an array of id's for example ["1","2","3"], and I want to query only users that their id is include in this array so it going like this

        if (ids){
            users = await pool.query(`SELECT * FROM users WHERE id IN ($1:csv)`, [members])
        }

I'm using pool.query on nodejs, but this command shown here is not working so I don't exactly knows how to do that, thanks for any help

try one of these https://stackoverflow.com/questions/11231544/check-if-value-exists-in-postgres-array — nbk, Jul 10 '23 at 15:21
1) What is `:csv` supposed to be doing? 2) Have you tried `... WHERE id = ANY($1)`? — Adrian Klaver, Jul 10 '23 at 15:39

Saif Ali · Answer 1 · 2023-07-10T20:40:19.533

2

This works if you are using the node-postgres npm module for data queries.

So after creating a Pool, use the following query structure:

    const query = "
  SELECT *
  FROM abc
  WHERE id IN (${ids.map((_, i) => `$${i + 1}`).join(',')});
";

and then run the pool.query command passing the query as the parameter.

However please note that the query uses array mapping, and this approach can be a bit expensive.

#postgresSQL

edited Jul 10 '23 at 20:40

answered Jul 10 '23 at 20:39

Saif Ali

53
3

I'm actually using pg. but Adrian Klaver comment ```WHERE id = ANY($1)``` worked – Niv Jul 11 '23 at 18:28

postgreSQL query only users that their id is in the array

1 Answers1