Menu
I'm using Postgres with this query
select * from Entity this_ where (this_.ID not in (null)) Why does this give me no results? I would expect to get all rows where id is not null
with
(this_.ID not in (1)) i get the expected results
904
Here is an example of how to use the PostgreSQL IS NOT NULL condition in a SELECT statement: SELECT * FROM employees WHERE first_name IS NOT NULL; This PostgreSQL IS NOT NULL example will return all records from the employees table where the first_name does not contain a null value.
PostgreSQL NULLIF function syntaxThe NULLIF function returns a null value if argument_1 equals to argument_2 , otherwise it returns argument_1 .
The not-null constraint in PostgreSQL ensures that a column can not contain any null value. This is a column constraint. No name can be defined to create a not-null constraint. This constraint is placed immediately after the data-type of a column. Any attempt to put NULL values in that column will be rejected.
PostgreSQL IS NOT NULL operator.
The result of [not] in (null) will always be null. To compare to null you need is [not] null or is [not] distinct from null
select * from Entity this_ where this_.ID is not null If you want where (ID not in (1,null)) as in your comment you can do
where ID is not null and ID not in (1) PostgreSQL uses NULL as undefined value.
What you're asking is to return the items that are not in a list or an undefined value. Since undefined means that you do not know what's inside, PostgreSQL does not return any item because simply can not respond to the request.
While the request:
select * from Entity where id in (1, null) can return the records, because if it finds an element with ID = 1 knows that is in the collection
the request:
select * from Entity where (ID not in (1, null)) can not be satisfied because the null value can be any value.
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
