Guide to using ANY in Postgres, instead of IN

Nice guide on how to use ANY in postgres

The IN operator has to be in the top-3 SQL-specific operators that people learn, the close competitors are LIKE and BETWEEN. Thus, IN feels familiar when crafting SQL: you can use it with nested SQL statements, or create your own list. But, it does have limitations — what if you wanted to send a list of unknown length? You can’t really use SQL placeholders without also modifying the SQL statement.

What if I told you there was a less ubiquitous operator that offered all of the power and less of the drawbacks. In this article we’ll talk about using = ANY(array) in instead of IN(list) to improve code and query quality.

We don’t give Java enough love in our tutorials, so these examples are written for JDK with the Vert.x reactive Postgres library. Don’t worry, you’ll be able to read it. We chose a direct Postgres client because ORMs typically default to IN(list), and I’m making an argument they should convert to = ANY(array) where applicable.