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?

how about having an if at top i.e. `if not season_id` or vice versa? — SMA, Mar 04 '18 at 11:19
[what is “=null” and “ IS NULL”](https://stackoverflow.com/questions/2749044/what-is-null-and-is-null) — Lukasz Szozda, Mar 04 '18 at 11:19

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

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;

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)` — ypicard, Mar 05 '18 at 09:06

Py-postgresql: WHERE param = None not working

1 Answers1