Postgres query for IN(NULL, 'test') does not work

Question

When I wan't to match a column that has some certain string values or is null, I assumed I can do something like this:

SELECT * FROM table_name WHERE column_name IN (NULL, 'someTest', 'someOtherTest');

But it does not return the columns where column_name set set to NULL. Is this anywhere documented? Why does it not work?

Answer 1

You can't compare NULL values using = (which is what IN is doing).

Quote from the manual

Ordinary comparison operators yield null (signifying “unknown”), not true or false, when either input is null. For example, 7 = NULL yields null, as does 7 <> NULL

You need to add a check for NULL explicitly:

SELECT * 
FROM table_name 
WHERE (column_name IN ('someTest', 'someOtherTest') OR column_name IS NULL);

Answer 2

NULL and empty string (i.e ' ') both are considered different in Postgres, unlike Oracle.

The query can be modified as:

SELECT * 
FROM table_name 
WHERE (column_name IN ('someTest', 'someOtherTest', '', ' ') OR 
column_name IS NULL);