Postgresql and comparing to an empty field

Question

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.

Possible duplicate with various other questions, e.g., http://stackoverflow.com/q/1833949/141081, http://stackoverflow.com/q/3777230/141081, ... — Dirk, Nov 08 '10 at 18:40

Mark Byers · Accepted Answer · 2010-11-08T19:25:56.113

5

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.

edited Nov 08 '10 at 19:25

answered Nov 08 '10 at 18:47

Mark Byers

811,555
193
1,581
1,452

it seems that IS DISTINCT FROM did the job too – spacemonkey Nov 08 '10 at 18:50
1

@spacemonkey: You're right. That's actually a better way to do it in PostgreSQL. I completely overlooked it. – Mark Byers Nov 08 '10 at 19:00

score 2 · Answer 2 · answered Nov 08 '10 at 18:48

2

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

answered Nov 08 '10 at 18:48

Kuberchaun

29,160
7
51
59

Postgresql and comparing to an empty field

2 Answers2