How to bind array to IN condition with Node postgres

Question

I'm struggling to figure out of to bind an array to an IN query in node pg library.

  const env = 'foo';
  const sourceTypes = ['one', 'two'];

  const resp = await pool.query(
    `SELECT source_id, target_id FROM records
    WHERE target_env = $1
    AND source_type IN ($2)`,
    [env, sourceTypes],
  );

I've tried several variations and they either error out or don't return any data.

I can make the query when I just use when I manually bind to generate something like this:

SELECT source_id, target_id FROM records
    WHERE target_env = 'foo'
    AND source_type IN ('one', 'two')

PS: If can provide suggestion on how to actually see the SQL request that PG is generating, that would be extremely helpful!

jjanes · Accepted Answer · 2020-11-18 15:15:28Z

6

You can't bind an array to multiple elements of a list in one go. But you can use an array instead of a list by changing in ($2) to =ANY($2)

You can see what queries are getting sent by setting log_statement=all and then viewing the PostgreSQL log file.

answered Nov 18, 2020 at 15:15

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

randomsock Over a year ago

Pretty much the exact same example here, but using =ANY($1) is giving error: malformed array literal: "ABC123" with params simply ['ABC123']. Was there anything else subtle I've missed?

randomsock Over a year ago

oh wait, it's me ... should be double brackets: an array of parameters to the query, and the ANY parameter itself which is an array, so in my example [['ABC123']]. Works a treat now - thanks @jjanes

Collectives™ on Stack Overflow

How to bind array to IN condition with Node postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related