How I can filter SQL results with != in PostgreSQL SQL query? Example
SELECT * FROM "A" WHERE "B" != 'C'
Working. But it's also filtered all record where "B" IS NULL. When I changed query to:
SELECT * FROM "A" WHERE "B" != 'C' OR "B" IS NULL
I'm got right result. O_o. Always, when I need using != I need also check OR "field" IS NULL? Really?
It's uncomfortable in Sequelize: { B: { $or: [null, { $not: 'C' }] } }, instead: { B: { $not: 'C' } } :(
You can use the "null safe" operator is distinct from instead of <>
SELECT *
FROM "A"
WHERE "B" is distinct from 'C'
http://www.postgresql.org/docs/current/static/functions-comparison.html
You should also avoid quoted identifiers. They are much more trouble then they are worth it
IS NOT DISTINCT FROM is exactly what I needed in order to compare equality with a value that could be null. Thank you for leading me to it!In PostgreSQL:
<> or != means Not equal, but cannot treat NULL as a comparable value.
IS DISTINCT FROM means Not equal, and can treat NULL as a comparable value.
So for example, there is person table as shown below:
postgres=# \pset null NULL
Null display is "NULL".
postgres=# SELECT * FROM person;
id | name | age
----+--------+------
1 | John | 27
2 | David | 32
3 | Robert | NULL
4 | Mark | NULL
(4 rows)
Then, <> or != cannot get the row where age in NULL as shown below:
postgres=# SELECT * FROM person WHERE age <> 27;
id | name | age
----+-------+-----
2 | David | 32
(1 row)
Or:
postgres=# SELECT * FROM person WHERE age != 27;
id | name | age
----+-------+-----
2 | David | 32
(1 row)
But, IS DISTINCT FROM can get the row where age in NULL as shown below:
postgres=# SELECT * FROM person WHERE age IS DISTINCT FROM 27;
id | name | age
----+--------+------
2 | David | 32
3 | Robert | NULL
4 | Mark | NULL
(3 rows)
In addition, <> or != and IS NULL can get the row where age in NULL as shown below:
postgres=# SELECT * FROM person WHERE age <> 27 OR age IS NULL;
id | name | age
----+--------+------
2 | David | 32
3 | Robert | NULL
4 | Mark | NULL
(3 rows)
Or:
postgres=# SELECT * FROM person WHERE age != 27 OR age IS NULL;
id | name | age
----+--------+------
2 | David | 32
3 | Robert | NULL
4 | Mark | NULL
(3 rows)
you can use case when in where clause: its treat null values as an empty string. So it will return the null data also.
select * from table_A
where ( case when "column_a" is null then '' else "column_a" end !='yes')
It's pretty faster as well.
