CODEWITHSUNDEEP
postgresqlselectnullcompare
spacemonkey
spacemonkey

Reputation: 19964

Postgresql and comparing to an empty field

It seems that in PostgreSQL, empty_field != 1 (or some other value) is FALSE. If this is true, can somebody tell me how to compare with empty fields?

I have following query, which translates to "select all posts in users group for which one hasn't voted yet:

SELECT p.id, p.body, p.author_id, p.created_at
FROM posts p
LEFT OUTER JOIN votes v ON v.post_id = p.id
WHERE p.group_id = 1 
AND v.user_id != 1

and it outputs nothing, even though votes table is empty. Maybe there is something wrong with my query and not with the logic above?

Edit: it seems that changing v.user_id != 1, to v.user_id IS DISTINCT FROM 1, did the job. From PostgreSQL docs:

For non-null inputs, IS DISTINCT FROM is the same as the <> operator. However, when both inputs are null it will return false, and when just one input is null it will return true.

Upvotes: 7

Views: 11311

Answers (2)

Mark Byers
Mark Byers

Reputation: 838216

If you want to return rows where v.user_id is NULL then you need to handle that specially. One way you can fix it is to write:

AND COALESCE(v.user_id, 0) != 1

Another option is:

AND (v.user_id != 1 OR v.user_id IS NULL)

Edit: spacemonkey is correct that in PostgreSQL you should use IS DISTINCT FROM here.

Upvotes: 5

Kuberchaun
Kuberchaun

Reputation: 30324

NULL is a unknown value so it can never equal something. Look into using the COALESCE function.

Upvotes: 2

Related Questions