How to count null values in postgresql?

Question

select distinct "column" from table;

output:

    column
1     0.0
2     [null]
3     1.0

But when I try to count the null values

select count("column") from train where "column" is NULL;

Gives output 0 (zero)

Can you suggest where it's going wrong?

Solution 1

Use count(*):

select count(*) from train where "column" is NULL;

count() with any other argument counts the non-NULL values, so there are none if "column" is NULL.

Solution 2

Use SUM

SELECT SUM(CASE WHEN column IS NULL THEN 1 ELSE 0 END) AS column_null_tally
FROM table;

Solution 3

Some workaround when you want to count the values on aggregations, including NULL ones, but can't use count(*) (if other columns are different too).

On these cases, you can use this request :

count(distinct("column")) + (CASE bool_or("column" is null) WHEN true THEN 1 ELSE 0 END)

The count(distinct(column)) will count the non null values, and the other part will add 1 if there is a null value