I am trying to perform a read operation on my PostgreSQL database. I am using SpringTemplate and I am passing dynamic parameters to it.
"SELECT id, name FROM Student WHERE id IN :id"
What is the maximum number of elements I can pass in ID without breaking my JDBC connection and for the operation to work smoothly?
For the record, I don’t think there’s any problem on the JdbcTemplate side
“work smoothly” is rather subjective. But I’ve found that starting with 64 bind values, arrays seem to outperform in lists in PostgreSQL
In any case, there’s a hard limit of 32767 parameters per statement in PostgreSQL.
Now, in a lot of cases, your
ID list is the result of another query. If that list is not modified manually in the UI (e.g. via a set of check boxes), then why not just repeat that original query and turn your
IN predicate into a semi join? E.g.
SELECT id, name FROM Student WHERE id IN ( SELECT id FROM some_other_query WHERE some_previous_predicate )