CODEWITHSUNDEEP
pythonsqlpostgresql
leonsas
leonsas

Reputation: 4908

Return all values when a condition value is NULL

Suppose I have the following very simple query:

query = 'SELECT * FROM table1 WHERE id = %s'

And I'm calling it from a python sql wrapper, in this case psycopg:

cur.execute(query, (row_id))

The thing is that if row_id is None, I would like to get all the rows, but that query would return an empty table instead. The easy way to approach this would be:

if row_id:
    cur.execute(query, (row_id))
else:
    cur.execute("SELECT * FROM table1")

Of course this is non idiomatic and gets unnecessarily complex with non-trivial queries. I guess there is a way to handle this in the SQL itself but couldn't find anything. What is the right way?

Upvotes: 0

Views: 76

Answers (2)

Wietze314
Wietze314

Reputation: 6020

SELECT * FROM table1 WHERE id = %s OR %s IS NULL

But depending how the variable is forwarded to the query it might be better to make it 0 if it is None

SELECT * FROM table1 WHERE id = %s OR %s = 0

Upvotes: 1

Robert
Robert

Reputation: 25753

Try to use COALESCE function as below

query = 'SELECT * FROM table1 WHERE id = COALESCE(%s,id)'

Upvotes: 1

Related Questions