NULL values in coulmn are not comparable via equality operator in POSTGRES SQL

Question

I wanted to get records from a single table where end_date column values are same of particular so_line_id and state value. Due to that I do self join from table itself

id	so_line_id	field	value	end_date	create_date
1	4040	product	submitted	2022-02-07 02:03:40	2022-02-07 02:03:30
2	4040	product	pending_fulfillment	2022-02-07 03:03:10	2022-02-07 02:03:40
3	4040	product	active	NULL	2022-02-07 03:03:10
4	4040	product	active	NULL	2022-02-15 06:20:10

I tried to get result via query mention below select * from product_history h1 join product_history h2 on h1.so_line_id = h2.so_line_id and h2.id != h1.id and h1.end_date = h2.end_date where h1.so_line_id = 4040;

Above completely works fine when we have datetime values instead of NULL.

If you want to include "NULL equals to NULL", you can do `col1 is not distinct from col2`. — jarlh, May 11 '23 at 08:08
@nbk Yes it is but didn't find it. I understand the issue from blog https://www.percona.com/blog/handling-null-values-in-postgresql/ — Abdul Basit, May 11 '23 at 08:15
Null Handling is in every database equal as it is defined as stadard and every Database system has its own NULL safe equal handling, so you would find actually many many threads for that. — nbk, May 11 '23 at 08:23
nukll safe comparison on Posthgres https://stackoverflow.com/questions/33828329/why-use-is-distinct-from-postgres — nbk, May 11 '23 at 08:28

score 0 · Answer 1 · edited May 11 '23 at 08:00

We need to understand that NULL means no value in PostgreSQL. It does not equal 0, empty string, or spaces. So no equality operators works with NULL values.

select null = null;

It is but we don't get any results.

Instead of = or != we should use is or is not statements.

select null is null;

?column?
true

Now I change my query to tested null values via the is statement and also use the equality operator to test datetime object if there

select * from product_history h1
join product_history h2 on h1.so_line_id = h2.so_line_id and h2.id != h1.id
and
(
(h1.end_date = h2.end_date)
OR
(h1.end_date is null and h2.end_date is null)
)
where h1.so_line_id = 4040;

The IS is a part of the , it's not a statement. – jarlh May 11 '23 at 08:34 — jarlh, May 11 '23 at 08:34

NULL values in coulmn are not comparable via equality operator in POSTGRES SQL

1 Answers1