Not equal and null in Postgres

Question

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

Fibroblast

Answer 1

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}

Insufficiency

"You should also avoid quoted identifiers. They are much more trouble then they are worth it" ...this is preposterous – Prig

@Prig wiki.postgresql.org/wiki/… – Insufficiency

I understand that the convention is to prefer lowercase, but (1) that's not always an option and (2) even with lowercase identifiers, always-quoting can be a major readability improvement for the all-too-common case of raw-SQL-as-string in source code. – Prig

@a_horse_with_no_name This is awesome!!!! I wish hibernate generated all it's named queries like this. It alleviates so much confusion. Now if only I could configure a databse to have performance with unique null values in an index instad of coalesce in my index which I guess functions destroy the lookup into the index(when defining uniqueness I mean for firstname, middle, last). – Augustaaugustan

Is this SQL standard complaint too I think, right @a_horse_with_no_name ? or am I wrong there? – Augustaaugustan

@DeanHiller: yes, it's part of the SQL standard. – Insufficiency

The inverse 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! – Excogitate

as @Prig states, sometimes quoted identifiers can be useful. I have tables coming in from three autonomous legacy systems, each with a segment field. One is segment, one is SEGMENT, and guess the third (yes, Segment). Each system has its own idea of 'segment'. I decided to quote every source field verbatim in my code. "SEGMENT" means something distinct from "Segment" and "segment", no need to add artificial suffixes or change the name of the field, which would be horrible when trying to find it in documentation. I know right off which segment is referenced by a statement. – Nimbus

Answer 2

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.

Concerned

Answer 3

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)

Infallibilism

This gives the same answer as the accepted answer – Pelf