postgres: Why does SELECT NULL <> '' return FALSE?

Question

I recently was running a query on a data column that contained strings, empty strings and nulls. I wanted to keep everything but the empty strings so naturally I did something like

WHERE my_row <> ''

However I discovered that this also removed my nulls. :(

I did a little poking around and found

SELECT NULL = ''  -- Returns False. No surprise here

But

SELECT NULL <> ''  -- Also returns False. Huh?

Can someone explain this to me?

user2859458 · Accepted Answer · 2020-01-08 16:44:46Z

1

Turns out whenever you do a standard comparison operator against a NULL, you get FALSE, NOT TRUE or NULL back.

If you want to do comparisons on a column that has NULL in it, you can use IS [NOT] DISTINCT FROM in place of = or <> and it will treat NULL like a value.

I used the following and it removed my empty strings without removing my NULLs

WHERE my_row IS DISTINCT FROM ''

https://www.postgresql.org/docs/current/functions-comparison.html

answered Jan 8, 2020 at 16:44

user2859458

2,5552 gold badges23 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Belayer Over a year ago

That is because when NULL is involved you are no longer looking at binary (Boolean) 2-state logic where results are always True, False. With nulls you are working tertiary or 3-state logic where the results are True, False, or Null. Any comparison operator applied against Null results in Null.

Collectives™ on Stack Overflow

postgres: Why does SELECT NULL <> '' return FALSE?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related