how to pass a list into postgres as a parameter-CodePudding

I am trying to run this with typeorm

const fields = await queryRunner.query(
  `SELECT "fieldId", "name" FROM "field" WHERE "name" IN ($1) `,
  [...fieldNames]
);

where fieldNames is a list. But I get an error saying bind message supplies 5 parameters

Is there anyway to make this dynamic so i can pass a list and get returned values? This isn't unique to type orm -- it's an issue with postgres

CodePudding user response：

Each element of an IN query is a separate parameter. If you want to pass five elements you need five parameters.

SELECT "fieldId", "name"
FROM "field"
WHERE "name" IN ($1,$2,$3,$4,$5)

This means dynamically generating the SQL to match the number of values you have.

Postgres offers a better way. Use the any operator. This works with an array which is a single value.

SELECT "fieldId", "name"
FROM "field"
WHERE "name" = ANY($1)

Now you should be able to pass an array of values as your first parameter. Typeorm should convert it to a Postgres Array.

const sql = `
  select fieldId, name
  from field
  where name = ANY($1)
`
const values = [['manny','moe','jack']]
client.query(sql, values)

This should result in:

  select fieldId, name
  from field
  where name = ANY(ARRAY['manny','moe','jack'])