Postgresql 并与空字段进行比较
似乎在 PostgreSQL 中，empty_field != 1（或其他值）是FALSE。如果这是真的，有人可以告诉我如何与空字段进行比较吗？
我有以下查询，它翻译为“选择用户组中尚未投票的所有帖子：
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
并且它不输出任何内容，即使 votes 表为空。也许我的有问题查询而不是上面的逻辑？
编辑： 似乎将 v.user_id != 1 更改为 v.user_id IS DISTINCT FROM 1 ，完成了工作。 来自 PostgreSQL 文档：
对于非空输入，与 与 <> 相同操作员。 然而，当两个输入都为空时 将返回 false，并且当只有一个时 输入为 null 则返回 true。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果你想返回 v.user_id 为 NULL 的行，那么你需要特殊处理。修复它的一种方法是编写：
另一种选择是：
编辑：spacemonkey 是正确的，在 PostgreSQL 中，您应该在此处使用
IS DISTINCT FROM。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:
Another option is:
Edit: spacemonkey is correct that in PostgreSQL you should use
IS DISTINCT FROMhere.NULL 是一个未知值，因此它永远不能等于某个值。研究使用 COALESCE 函数。
NULL is a unknown value so it can never equal something. Look into using the COALESCE function.