Prepared statements with parameterized IN operator fail

[ChristianSiegert]

I have a simple query that looks like this in SQL:

SELECT * FROM table WHERE id IN (123, 456);

Using your project I would write it like this:

const ids = [123, 456]; // Untrusted input
await poolClient.queryObject<MyType>({
  args: [ids],
  camelcase: true,
  text: "SELECT * FROM table WHERE id IN ($1)",
});

But this gives me an error in Postgres:

ERROR:  invalid input syntax for type integer: "{"123","456"}"
CONTEXT:  unnamed portal parameter $1 = '...'
STATEMENT:  SELECT * FROM table WHERE id IN ($1)

How does one write this query?

[Soremwar]

Hi, yeah Postgres won't allow you to use the IN operator with an array of data, so you'll have to do the following

const ids = [123, 456]; // Untrusted input
await poolClient.queryObject<MyType>({
  args: [ids],
  camelcase: true,
  text: "SELECT * FROM table WHERE ID = ANY($1)",
});

https://stackoverflow.com/questions/10738446/postgresql-select-rows-where-column-array

[ChristianSiegert]

Thank you. On a side note: I think an example like this would be helpful in the documentation because some other SQL libraries do some rewriting and allow WHERE field IN (?) which tripped me up here.