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

Question

This is a pretty tough question to Google, so hopefully someone can help me out here. In Postgres, you can specify an IN clause, like:

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

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

Thanks.

Answer 1

From here: http://www.phpbuilder.com/board/archive/index.php/t-10250064.html

Sounds like it's only limited by your query size, and that is maybe bounded at 16 megs. How many are you looking at?

Answer 2

what's the max number of parameters you can pass into IN?

If you have to ask this question, you should probably load the values into a temporary table and then run a JOIN against that temp table instead of an IN predicate.

Answer 3

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
);

Answer 4

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.
 */