Py-postgresql中WHERE子句传入None匹配NULL失效问题求助

Hey there, let's break down why your initial approach wasn't working and confirm the fix you found is totally correct.

The root of this issue is a core rule in SQL (including PostgreSQL): NULL represents an unknown value, so standard comparison operators like = don't work with it. When you use season_id = $2 and pass None (which py-postgresql maps to PostgreSQL's NULL), PostgreSQL evaluates NULL = NULL as logically false (specifically, it returns NULL instead of TRUE). That's why no matching records come back—this isn't a bug in the py-postgresql driver, it's intentional SQL behavior.

Your workaround using season_id IS NULL is exactly the right call! IS NULL is the dedicated SQL syntax for checking if a column holds a NULL value, and it avoids the oddities of comparing unknown values.

If you need a more flexible query that can handle both regular values and NULL for season_id without writing two separate statements, you can use PostgreSQL's IS NOT DISTINCT FROM operator. This treats NULL as equal to NULL, so you can reuse the same prepared statement regardless of whether you're passing a concrete value or None:

db.prepare("SELECT * FROM seasons WHERE user_id=$1 AND season_id IS NOT DISTINCT FROM $2 LIMIT 1")

When you pass a regular value (like an integer) as $2, it behaves just like the = operator. When you pass None, it will match rows where season_id is NULL.

内容的提问来源于stack exchange，提问作者ypicard