Array In Where Clause (Works with 1 item fails with more)

Queries and Resources
1

  • Goal: I am trying to use an array variable in a postgres SQL query to exclude any items with an id matching an array item.

  • Steps: I have written an SQL query that uses the != ANY() form. The query works when there is only 1 item in the array and excludes items matching that item. The query does not work when there is more than 1 item in the array and none of the matching items are excluded.

My current where clause (This is in an insert statement):

WHERE
  start_time BETWEEN {{moment(cpy_wk_src.view.start)}} AND {{moment(cpy_wk_src.view.end)}} AND staff_id != ANY({{ exclude_from_copy.value }}) ;

Any ideas on why this isn't working?

2

You may actually want != ALL(), which would test that the value is not in the array at all. != ANY() will evaluate true if staff_id doesn't equal ANY of the items in the array.

3

Thank you fellow JG! This works!

1 Like