Menu
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.
343
Example - With SELECT Statement Let's look at an example of how to use PostgreSQL IS NULL in a SELECT statement: SELECT * FROM employees WHERE first_number IS NULL; This PostgreSQL IS NULL example will return all records from the employees table where the first_name contains a NULL value.
The PostgreSQL NULL is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank. A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different from a zero value or a field that contains spaces.
PostgreSQL does not support nvl functions, but it supports coalesce functions. The usage is the same with that in Oracle. You can utilize coalesce to convert nvl and coalesce functions of Oracle. The arguments have to be of the same type, or can be automatically converted to the same type.
Handling empty strings in PostgreSQL In Oracle, because empty strings are treated as NULL, the preceding insert statements #2 and #3 will store NULL for column tname in the table. However, in PostgreSQL, the table will store NULL for the #2 and an empty string for the #3 insert statements.
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.
87
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
