How Can I Find Null Values With SELECT Query In Psycopg?
Solution 1:
First thing to do is find out what query it's turning your command into:
print(cur.mogrify("SELECT id FROM registro WHERE id_movil = (%s);", (None,)))
If that gives you anything other than an IS NULL
codition check, it won't get NULLs from the table.
Specifically, if you see the phrase = NULL
, you'll know it's not being translated correctly and you'll have to do something like:
if var is None:
cur.execute("SELECT id FROM registro WHERE id_movil IS NULL;")
else:
cur.execute("SELECT id FROM registro WHERE id_movil = (%s);", (var,))
Or, if you know you're always looking for NULL
values (as your use of the constant None
seems to indicate), just use:
cur.execute("SELECT id FROM registro WHERE id_movil IS NULL;")
It's quite possible that only the %s
is replaced with a NULL
when the argument is None
, and it's not quite clever enough to go back and change =
into IS
(or <>
into IS NOT
).
This would explain why this works:
cur.execute("INSERT into mytbl value (%s);", (None,))
(because substitution of only the %s
gives the command you want), but anything with = NULL
will not act as expected, unless you've been burnt by it enough times to know what to expect :-).
Solution 2:
Try this
cur.execute("SELECT id FROM registro WHERE (id_movil = (%s) or id_movil is null);", (movil,))
Solution 3:
There's a different syntax for checking whether something is NULL
-valued:
cur.execute("SELECT id FROM registro WHERE id_movil IS NULL;")
The same applies for getting everything that is not NULL
:
cur.execute("SELECT id FROM registro WHERE id_movil IS NOT NULL;")
EDIT:
You can combine WHERE clauses using AND just like you normally would:
cur.execute(
"SELECT id FROM registro WHERE id_movil IS NULL AND name = (%s);",
('Bob',)
)
Solution 4:
An alternative which I'm currently experimenting with is a postgres setting called transform_null_equals
(https://www.postgresql.org/docs/13/runtime-config-compatible.html#GUC-TRANSFORM-NULL-EQUALS)
you can set it in postgresql.conf
or however you do your config (i'm using docker-compose for local dev and RDS for prod, so that means a parameter group).
transform_null_equals = 1
The effect is that postgres then just magically transforms any = NULL
queries into IS NULL
without you having to think about it! 🎉
Post a Comment for "How Can I Find Null Values With SELECT Query In Psycopg?"