CODEWITHSUNDEEP
postgresqlnull
user2859458
user2859458

Reputation: 2505

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

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?

Upvotes: 0

Views: 761

Answers (1)

user2859458
user2859458

Reputation: 2505

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

Upvotes: 1

Related Questions