CODEWITHSUNDEEP
pythonsqlpython-3.xpostgresql
New User
New User

Reputation: 149

Select Query with IS NULL and Values

I am a beginner with PostgreSQL and this is a critical problem for me.

My table looks like this:

test num_1 num_2
0    1     1
1
2

Several columns in my database have NULL or non-NULL values. I want to read out test using just one query, which can accept NULL as well as non-NULL values.

The queries should have the functionality given below:

First query:

x = 1
sql = ("""SELECT test FROM table WHERE num_1 = %s""")
df = pd.read_sql(sql, con = db, params = (x))

It should return 0.

Second query:

x = None
sql = ("""SELECT test FROM table WHERE num_1 IS NULL""")
df = pd.read_sql(sql, con = db, params = (x))

It should return 1,2.

How can achieve this using just one query? I tried the below code but it is not working correctly:

x = None
sql = ("""SELECT test FROM table WHERE num_1 = %s or num_1 IS NULL """)
df = pd.read_sql(sql, con = db, params = (x))

It returns 0,1,2. I believe it is happening because of the "or" in the query. I want to have just one query that can handle or take both the NULL or a value.

If I query for NULL it should return 1,2, and if I query for 1 it should return 0. I do not want 0,1,2.

Upvotes: 3

Views: 919

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247235

This is a case for IS NOT DISTINCT FROM which is like an = that treats NULL as a normal value:

SELECT test FROM table WHERE num_1 IS NOT DISTINCT FROM %s

The down side is that this condition cannot use an index.

If you need to use an index, you could write

SELECT test FROM table WHERE num_1 = %s
UNION ALL
SELECT test FROM table WHERE num_1 IS NULL AND %s IS NULL

Upvotes: 2

Related Questions