postgresql 如何检查数字是否为 NaN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25858859/
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
How to check if number is NaN
提问by Mike T
I need to test if a numeric/float value in PostgreSQL is not a number (NaN). Note that "PostgreSQL treats NaNvalues as equal", so this C++ trick doesn't work. As I'm not seeing any isnanfunction in PostgreSQL 9.3, here is my best attempt to make one:
我需要测试 PostgreSQL 中的数字/浮点值是否不是数字 (NaN)。请注意，“PostgreSQL 将NaN值视为相等”，因此此 C++ 技巧不起作用。由于我isnan在 PostgreSQL 9.3 中没有看到任何函数，这是我最好的尝试：
create or replace function isnan(double precision) returns boolean as
$$select ::text = 'NaN'::text$$ language sql;
Is there any better way to test for NaNs?
有没有更好的方法来测试NaNs？
采纳答案by Craig Ringer
Is there any better way to test for NaNs?
有没有更好的方法来测试 NaN？
Simply compare for equality:
简单比较是否相等：
SELECT double precision 'NaN' = double precision 'NaN';
as, per the docs you linked to, Pg treats NaNs as equal. I'm surprised by this, given that it correctly treats NULLas not equal to any other NULL, and would've expected NaN= NaNto return NULL, but ... well, it works.
因为，根据您链接到的文档，Pg 将NaNs 视为平等。我对此感到惊讶，因为它正确地将其NULL视为不等于任何其他NULL，并且期望NaN=NaN返回NULL，但是......好吧，它有效。
Or if you want a function:
或者如果你想要一个函数：
create or replace function isnan(double precision)
language sql
immutable
returns boolean as $$
select = double precision 'NaN'
$$;
回答by Podgorskiy
Any NaN value taken with a minus sign still is a NaN value (just like a zero), so you can try this:
任何带减号的 NaN 值仍然是 NaN 值（就像零），所以你可以试试这个：
create or replace function isnan(double precision) returns boolean as $$
select = - and != 0.0 $$ language sql;
or:
或者：
create or replace function isnan(double precision) returns boolean as $$
select = - and = + 1.0 $$ language sql;
As far as PostgreSQL treats NaN values greater than all non-NaN values, the following trick is possible:
就 PostgreSQL 处理的 NaN 值大于所有非 NaN 值而言，以下技巧是可能的：
create or replace function isnan(double precision) returns boolean as $$
select > 0 and - > 0 $$ language sql;
回答by jaycode
For Google BigQuery (and maybe other sql servers out there), use the following
对于 Google BigQuery（可能还有其他 sql 服务器），请使用以下内容
SELECT * FROM [project:dataset.table]
WHERE IS_NAN(field) = true
