Postgresql 并与空字段进行比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4126851/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql and comparing to an empty field
提问by spacemonkey
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?
似乎在 PostgreSQL 中，empty_field != 1（或其他一些值）是FALSE。如果这是真的，有人能告诉我如何与空字段进行比较吗？
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 votestable 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:
编辑：似乎改变v.user_id != 1, 到v.user_id IS DISTINCT FROM 1，完成了这项工作。来自 PostgreSQL 文档：
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.
对于非空输入，IS DISTINCT FROM 与 <> 运算符相同。但是，当两个输入都为 null 时，它将返回 false，而当只有一个输入为 null 时，它将返回 true。
采纳答案by Mark Byers
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:
如果要返回 v.user_id 为 NULL 的行，则需要特别处理。您可以修复它的一种方法是编写：
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 FROMhere.
编辑：spacemonkey 是正确的，在 PostgreSQL 中你应该IS DISTINCT FROM在这里使用。
回答by Kuberchaun
NULL is a unknown value so it can never equal something. Look into using the COALESCE function.
NULL 是一个未知值，所以它永远不可能等于某物。研究使用 COALESCE 函数。
