Null query in postgreSQL

Question

I am trying to find the number of rows in a table with a particular column value = NULL in PostgreSQL. This is my query:

SELECT count(*) 
FROM database.table 
WHERE place_id = 3 AND user = (NULL);

And I get the count as 0. But there a pretty number of rows matching the query. What am I doing wrong here?

... and user is null

jarlh
– jarlh

2018-01-09 15:50:55 +00:00
Commented Jan 9, 2018 at 15:50 — jarlh
– jarlh, Commented Jan 9, 2018 at 15:50
worked! thank you @jarlh

SUPARNA SOMAN
– SUPARNA SOMAN

2018-01-09 16:03:37 +00:00
Commented Jan 9, 2018 at 16:03 — SUPARNA SOMAN
– SUPARNA SOMAN, Commented Jan 9, 2018 at 16:03

Rogério Carvalho Batista · Accepted Answer · 2018-01-09 15:52:17Z

3

You should use IS NULL:

Select count (*) from database.table where place_id = 3 and user IS NULL;

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.) This behavior conforms to the SQL standard.

edited Jan 9, 2018 at 15:52

answered Jan 9, 2018 at 15:51

Rogério Carvalho Batista

4622 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

clemens Over a year ago

And the reverse is also true: NULL is neither equal nor not equal to NULL.

SUPARNA SOMAN Over a year ago

Thanks @Rogério Carvalho Batista

clemens · Accepted Answer · 2018-01-09 16:01:27Z

0

If the value NULL comes from a variable, you can use IS DISTINCT FROM or IS NOT DISTINCT FROM which can compare NULL values:

SELECT COUNT(*) from database.table
WHERE place_id = 3 and user IS NOT DISTINCT FROM NULL

This query will also work for other values, especially parameters. The query

SELECT COUNT(*) from database.table
WHERE place_id = 3 and user IS NOT DISTINCT FROM $1

will work for $1 = 'Tom' and even $1 = NULL.

answered Jan 9, 2018 at 16:01

clemens

17.9k12 gold badges52 silver badges71 bronze badges

Collectives™ on Stack Overflow

Null query in postgreSQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related