postgresqltypeormpostgres-10

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

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?

Solution

  • 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);