PostgreSQL NULL value cannot be found

Question

select * from test;

select * from test where name not in ('amtf');

Why?

Answer 1

As others have said, the problem here is, that you're comparing against a null value, so it returns nothing, because it considers it as false, and I'll go even further that even if you say where name <> 'admf' it wont work, and even if you add more rows it will ignore the null row, and it's not just in PostgreSQL, it doesn't work in SQL-Server or MySQL either.

As you can see in these db<>fiddles SQL-Server, MySQL, and PostgreSQL.

And the reason why it doesn't work is, because you're saying name should not equal a specific value. First name needs to be a value it should not be equal to a value, but when name is null it doesn't have a value, and even more for a side note null itself is not equal null.

The way to solve it is to convert it to a empty string by using COALESCE(name,'') or in SQL-Server you can also use isnull(name,''), and then compare it, or you can add or name is null which will return you all rows, including null, where name <> 'some value'.