PostgreSQL中的(x IS NOT NULL)vs(NOT x IS NULL)

概述为什么x IS NOT NULL不等于NOT x IS NULL？ 这段代码： CREATE TABLE bug_test ( id int, name text);INSERT INTO bug_testVALUES (1, NULL);DO $$DECLARE v_bug_test bug_test;BEGIN RAISE NOTICE '%: 为什么x IS NOT NulL不等于NOT x IS NulL？

这段代码：

CREATE table BUG_test (    ID int,name text);INSERT INTO BUG_testVALUES (1,NulL);DO $$DECLARE    v_BUG_test BUG_test;BEGIN    RAISE NOTICE '%: %',v_BUG_test,(v_BUG_test IS NulL);    RAISE NOTICE '%: %',(v_BUG_test IS NOT NulL);    RAISE NOTICE '%: %',(NOT v_BUG_test IS NulL);    SELECT *    INTO v_BUG_test    FROM BUG_test    WHERE ID = 1;    RAISE NOTICE '%: %',(NOT v_BUG_test IS NulL);END$$;DROP table BUG_test;

给出以下输出：

(,): t(,): f(,): f(1,): f ???(1,): t

虽然我希望得到这个输出：

(,): t <<<(1,): t

您必须区分两种情况：将一个ColUMN与NulL进行比较,或者将整个ROW(RECORD)与NulL进行比较.

请考虑以下查询：

SELECT    ID,txt,txt     IS NulL AS txt_is_null,NOT txt IS NulL AS not_txt_is_null,txt IS NOT NulL AS txt_is_not_nullFROM    (VALUES        (1::integer,NulL::text)    )     AS x(ID,txt) ;

你得到这个：

+----+-----+-------------+-----------------+-----------------+| ID | txt | txt_is_null | not_txt_is_null | txt_is_not_null | +----+-----+-------------+-----------------+-----------------+|  1 |     | t           | f               | f               | +----+-----+-------------+-----------------+-----------------+

我想,这就是你和我的期望.您正在检查一个ColUMN对NulL,并且您得到“txt IS NOT NulL”和“NOT txt IS NulL”是等效的.

但是,如果您进行不同的检查：

SELECT    ID,x       IS NulL AS x_is_null,NOT x   IS NulL AS not_x_is_null,x   IS NOT NulL AS x_is_not_nullFROM    (VALUES        (1,NulL)    )     AS x(ID,txt) ;

然后你得到

+----+-----+-----------+---------------+---------------+| ID | txt | x_is_null | not_x_is_null | x_is_not_null |+----+-----+-----------+---------------+---------------+|  1 |     | f         | t             | f             |+----+-----+-----------+---------------+---------------+

这可能是令人惊讶的.一件事看起来合理(x IS NulL)和(NOT x IS NulL)是彼此相反的.另一件事(事实上既不是“x IS NulL”也不是“x IS NOT NulL”都是真的),看起来很奇怪.

然而,这就是PostgreSQL documentation所说的应该发生的事情：

If the Expression is row-valued,then IS NulL is true when the row Expression itself is null or when all the row’s fIElds are null,while IS NOT NulL is true when the row Expression itself is non-null and all the row’s fIElds are non-null. Because of this behavior,IS NulL and IS NOT NulL do not always return inverse results for row-valued Expressions; in particular,a row-valued Expression that contains both null and non-null fIElds will return false for both tests. In some cases,it may be preferable to write row IS disTINCT FROM NulL or row IS NOT disTINCT FROM NulL,which will simply check whether the overall row value is null without any additional tests on the row fIElds.

我必须承认我不认为我曾经使用过针对null的行值比较,但我想如果可能存在,那么可能会有一些用例.无论如何,我认为这不常见.

总结

以上是内存溢出为你收集整理的PostgreSQL中的(x IS NOT NULL)vs(NOT x IS NULL)全部内容，希望文章能够帮你解决PostgreSQL中的(x IS NOT NULL)vs(NOT x IS NULL)所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。