Psycopg3 selecting null values

Question

I have started migrating some Python code from using psycopg2 to pscopyg3. It appears there is a change in how null values are accepted as query parameters in the newer version. Based on a read of the documentation for this topic in psycopg3 there isn't any mention of how to deal with null values.

Below is a simplified example of my use case that worked fine in psycopg2

cursor.execute("SELECT * FROM mock_table WHERE var_1 is %s", [None])

And the below error is produced now in psycopg3

psycopg.errors.SyntaxError: syntax error at or near "$1" LINE 1:

SELECT * FROM mock_table WHERE var_1 is $1

Answer 1

Filtering by NULL is not compatible with psycopg's server-side binding behaviour.

You can use IS DISTINCT FROM instead, or IS NOT DISTINCT FROM, as is now documented:

cur.execute('SELECT * FROM tbl WHERE col IS DISTINCT FROM %s', (None,))

Both of the general alternatives proposed in the docs also work:

# Use psycopg's formatting tools.
cur.execute(psycopg.sql.SQL('select * from tbl where col is {}'.format('NULL')))

# Use client-side binding
cur = psycopg.ClientCursor(conn)
cur.execute('select * from tbl where col is %s', (None,))