Quick Fix: The maximum number of parameters in the “IN” clause for PostgreSQL is 32,767.
The Problem:
PostgreSQL – max number of parameters in "IN" clause?
The Solutions:
Solution 1: Technical limit of values passable to Postgres
At least there is a technical limit of 32767 values (=Short.MAX_VALUE) passable to the PostgreSQL backend, using Posgresql’s JDBC driver 9.1.
Solution 2: Unrestricted number of parameters in IN clause
PostgreSQL doesn’t explicitly limit the number of arguments that can be specified in an IN clause. This is because PostgreSQL tries to generate a ScalarArrayOpExpr from the IN/NOT IN clause, but this is only possible if the inputs are all scalars (no RowExprs) and there is a suitable array type available. If not, PostgreSQL falls back to a boolean condition tree with multiple copies of the left-hand expression.
Solution 3: Values Scan on "*VALUES*"
In PostgreSQL, the maximum number of parameters that can be passed into an IN clause is 65,535. This is because the IN clause is implemented using a values scan, and the maximum number of rows that can be scanned in a single values scan is 65,535.
If you try to pass more than 65,535 parameters into an IN clause, you will get an error message. For example, the following query will fail with an error message:
SELECT * FROM user WHERE id IN (1000, 1001, ..., 65536);
To avoid this error, you can use a different method to specify the values in the IN clause. For example, you can use a subquery or a join.
Solution 4: Performance tests with IN parameter list of ~10’000 parameters
An experienced Oracle DB user tested a query with approximately 10,000 parameters in an IN list, retrieving prime numbers up to 100,000 from a table containing the first 100,000 integers by listing all the prime numbers as query parameters.
The test results showed that the query plan optimizer was not overloaded, and index usage was optimized as expected. The query was transformed to use = ANY({...}::integer[]), effectively leveraging indices.
However, an older thread on the pgsql-hackers mailing list cautions that planning such queries can still incur a non-negligible cost. Therefore, it’s advisable to proceed with caution when using IN parameter lists with a large number of parameters.
Solution 5: There is no limit to the number of elements that can be passed to IN clause.
The PostgreSQL IN clause does not have a limit on the number of parameters that can be passed to it. However, passing a large number of parameters to the IN clause can result in performance issues. This is because PostgreSQL will consider the list of parameters as an array and will perform a linear search for each element in the array for each row in the table.
To avoid performance issues, it is recommended to use a different approach for querying data with a large number of parameters. One approach is to use an INNER JOIN with a temporary table. This approach is more scalable because the PostgreSQL query optimizer can use hash joins and other optimizations to improve performance.
