CODEWITHSUNDEEP
sqldatabasepostgresqlnullcomparison-operators
faiwer
faiwer

Reputation: 1926

Not equal and null in Postgres

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' } } :(

Upvotes: 80

Views: 56065

Answers (3)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

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)

Upvotes: 5

Ramesh Ponnusamy
Ramesh Ponnusamy

Reputation: 1797

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.

Upvotes: 3

user330315
user330315

Reputation:

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

Upvotes: 174

Related Questions