PostgreSQL - max number of parameters in "IN" clause?

Admin 1
Viewed 141522

In Postgres, you can specify an IN clause, like this:

SELECT * FROM user WHERE id IN (1000, 1001, 1002)

Does anyone know what's the maximum number of parameters you can pass into IN?

postgresql
8 Answers

According to the source code located here, starting at line 850, PostgreSQL doesn't explicitly limit the number of arguments.

The following is a code comment from line 870:

/*
 * We try to generate a ScalarArrayOpExpr from IN/NOT IN, but this is only
 * possible if the inputs are all scalars (no RowExprs) and there is a
 * suitable array type available.  If not, we fall back to a boolean
 * condition tree with multiple copies of the lefthand expression.
 * Also, any IN-list items that contain Vars are handled as separate
 * boolean conditions, because that gives the planner more scope for
 * optimization on such clauses.
 *
 * First step: transform all the inputs, and detect whether any are
 * RowExprs or contain Vars.
 */
user_275360

Just tried it. the answer is -> out-of-range integer as a 2-byte value: 32768

user_45534140

You might want to consider refactoring that query instead of adding an arbitrarily long list of ids... You could use a range if the ids indeed follow the pattern in your example:

SELECT * FROM user WHERE id >= minValue AND id <= maxValue;

Another option is to add an inner select:

SELECT * 
FROM user 
WHERE id IN (
    SELECT userId
    FROM ForumThreads ft
    WHERE ft.id = X
);
user_1018110
Related