PostgreSQL using ANY instead of IN

I recently learned while reading psycopg’s documentation (a Python driver for PostgreSQL), that you should use WHERE id = ANY(:values) instead of WHERE id IN :values when filtering over lists. That’s because the ANY operator works with empty list while IN doesn’t. Psycopg will also correctly adapt lists (even empty) in this case and won’t convert them to arrays like it does with IN. Tuples and set cannot be used with neither ANY nor IN.

This could help to simplify some queries.

As far as I know, SQLite and Mysql don’t have a similar operator.

Here is a small script to test by yourselves. You need to have a PG running accepting all connection. This can be done with: docker run -it --rm -p 5432:5432 -e POSTGRES_HOST_AUTH_METHOD=trust postgres:latest The script can be run with uv like this to install the dependencies before running the script: uv run postgresql-any.py

# To make this executable directly with uv run:
# /// script
# dependencies = [
#   "psycopg[binary]",
#   "sqlalchemy",
# ]
# ///

from sqlalchemy import create_engine, text


connection_url = "postgresql+psycopg://postgres@localhost:5432/postgres"

for query in (
    "SELECT * FROM test WHERE id IN :ids",
    "SELECT * FROM test WHERE id = ANY(:ids)",
):
    for ids in ([1, 2], [], (1, 2), {1, 2}):
        print(query, ids)
        engine = create_engine(connection_url)
        with engine.connect() as connection:
            connection.execute(text("CREATE TEMPORARY TABLE test (id serial PRIMARY KEY)"))
            connection.execute(text("INSERT INTO test(id) VALUES(1)"))
            connection.execute(text("INSERT INTO test(id) VALUES(2)"))
            connection.execute(text("INSERT INTO test(id) VALUES(3)"))

            try:
                results = connection.execute(
                    text(query),
                    {"ids": ids},
                )
                print(f"with {type(ids)}", results.fetchall())
            except Exception as e:
                print(f"Failed with {type(ids)}:", e)

        print("\n\n")
        engine.dispose()