Py-postgresql: WHERE param = None not working

Question

I am using python3.6 and py-postgresql==1.2.1. I have the following statement:

db.prepapre("SELECT * FROM seasons WHERE user_id=1ドル AND season_id=2ドル LIMIT 1), where season_id can be NULL.

I want to be able to be able to get the latest record with a NULL season_id by passing None as the 2ドル param, but it does not work. Instead, I need to create this second statement: db.prepapre("SELECT * FROM seasons WHERE user_id=1ドル AND season_id IS NULL LIMIT 1)

It must have something to do with season_id = NULL not working and season_id IS NULL is, but is there a way to make this work?

• SMA: how about having an if at top i.e. if not season_id or vice versa?
• Lukasz Szozda: what is "=null" and " IS NULL"

Answer 1

From Comparison Functions and Operators:

Do not write expression = NULL because NULL is not "equal to" NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.)

Some applications might expect that expression = NULL returns true if expression evaluates to the null value. It is highly recommended that these applications be modified to comply with the SQL standard. However, if that cannot be done the transform_null_equals configuration variable is available. If it is enabled, PostgreSQL will convert x = NULL clauses to x IS NULL.

and:

19.13.2. Platform and Client Compatibility

transform_null_equals (boolean)

When on, expressions of the form expr = NULL (or NULL = expr) are treated as expr IS NULL, that is, they return true if expr evaluates to the null value, and false otherwise. The correct SQL-spec-compliant behavior of expr = NULL is to always return null (unknown). Therefore this parameter defaults to off.

---

You could rewrite your query:

SELECT * 
FROM seasons 
WHERE user_id = 1ドル 
 AND (season_id = 2ドル OR (2ドル IS NULL AND season_id IS NULL))
-- ORDER BY ... --LIMIT without sorting could be dangerous 
 -- you should explicitly specify sorting
LIMIT 1;

• ypicard: Great. Thank you for the extra info too :)
• ypicard: If you don't want any result when the season_id param is not null and doesn't match any record: SELECT * FROM seasons WHERE user_id = 1ドル AND (season_id = 2ドル OR (2ドル IS NULL AND season_id IS NULL)