Geek Answers Handbook

Postgresql and compared to an empty field

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

I have the following query, which translates as "select all entries in the user group for which they have not voted:

 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 doesn’t display anything, even if the vote table is empty. Maybe something is wrong with my request, and not with the logic above?

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

For non-empty inputs, IS DISTINCT FROM matches the <> operator. However, when both inputs are zero, it will return false, and when there is only one input is null, it will return true.

+6
null select postgresql compare
source share
2 answers

If you want to return strings where v.user_id is NULL, then you will need to handle this specially. One way to fix this is to write:

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

Another variant:

 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.

+4
source share

NULL is an unknown value, so it can never be compared to something. Take a look at using the COALESCE function.

+2
source share

More articles:


All Articles