当检查行类型时，为什么IS NOT NULL返回false？

Question

我有一个名为registration()的函数，它应该在特定情况下向表中添加一行。我附上了一小段代码和调用的输出。

如果select *返回非空表行（根据RAISE NOTICE确实如此），我想引发异常而不是添加行。示例似乎表明rowt不为null，但是rowt IS NOT NULL返回f（并且异常未被引发）。

我希望这只是我没有注意到的小问题。

select * into rowt from Email where email_email = eml;
RAISE NOTICE '%, rowt IS NOT NULL:%',rowt, rowt IS NOT NULL;
if rowt IS NOT NULL THEN
   RAISE EXCEPTION 'email address, %, already registered.' , eml;
END IF;

输出:

NOTICE:  (7,,,), rowt IS NOT NULL:f

registration 
--------------
    21
(1 row)

CREATE TABLE IF NOT EXISTS Email ( 
   email_email VARCHAR(50) NOT NULL, 
   email_password VARCHAR(50) NOT NULL,
   email_id integer DEFAULT nextval('email_email_id_seq'::regclass) NOT NULL,
   email_person_id integer
);
CREATE OR REPLACE FUNCTION registration( wr text ) RETURNS integer AS $rL$
DECLARE
    eml text;
    pwd text;
    nm text;
    rle text;
    emid integer;
    rowt Email%ROWTYPE;
BEGIN
    eml := getWebVarValue( wr , 'email' );
    select * into rowt from Email where email_email = eml;
    RAISE NOTICE '%, rowt IS NOT NULL:%', rowt, rowt IS NOT NULL;
    IF rowt IS NOT NULL THEN
       RAISE EXCEPTION 'email address, %, already registered.' , eml;
    END IF;
    pwd := getWebVarValue( wr , 'password' );
    IF pwd IS NULL THEN
       RAISE EXCEPTION 'No password specified in registration.';
    END IF;
    INSERT INTO Email VALUES (eml,pwd) RETURNING Email.email_id INTO emid;
    --nm =  getWebVarValue( wr , 'name' );
    --rle = getWebVarValue( wr , 'role' );
    RETURN emid;
END;
$rL$ LANGUAGE plpgsql;

- LenB

---

我想要引发异常而不添加行 --- 那并发调用呢？如果有两个调用同时通过了这个检查呢？ - zerkms

你能否编辑你的帖子并展示Email函数的整个功能和表结构？根据当前信息，无法确定问题所在。可能的一个原因是：rowt是一个记录类型变量，而IS NOT NULL应该用于记录字段。 - Patrick

请参阅"使用单行结果执行查询"，以及特殊的FOUND变量。 - Mike T

@Kevin：注意：SET ISOLATION LEVEL SERIALIZABLE 可能会对数据库负载产生负面影响。它会显著增加锁的数量。 - Pavel Stehule

@Pavel：Postgres使用MVCC；它不设置锁定（除了谓词锁定之外，谓词锁定是非阻塞的，仅用于跟踪交易之间的读写关系）。 - Kevin

Answer 1

<row-type> 不是空

正如@Pavel提供的，检查<row-type> 不是空并不像你期望的那样工作。它只有在每一列都不为空时才返回TRUE。

你可以反转测试表达式：

IF <b>rowt IS NULL</b> THEN
   -- do nothing
ELSE 
   RAISE EXCEPTION 'email address, %, already registered.' , eml;
END IF;

任何一行都至少包含一个非空列，因此只有在没有找到任何内容时，rowt IS NULL 才会返回 TRUE。

参见：

• 当变量被设置时，IS NOT NULL测试记录不返回TRUE
• 一组列的NOT NULL约束

尽管如此，对于允许所有空行的表来说，这还是一个特殊情况。

更好的解决方案

请使用特殊变量FOUND 进行测试（像 @Mike commented）:

PERFORM FROM email WHERE email_email = eml;

IF FOUND THEN
   RAISE EXCEPTION 'email, %, already registered.', eml;
END IF;

由于我们实际上对返回的行不感兴趣，因此将 SELECT 替换为 PERFORM 以丢弃结果。任何一个都会相应地设置特殊变量 FOUND。
而且 SELECT 列表（或者说 PERFORM 列表）可以为空，因为只有行的存在才是重要的。

更简单的方法是使用 EXISTS：

IF EXISTS (SELECT FROM email WHERE email_email = eml) THEN
   RAISE EXCEPTION 'email, %, already registered.', eml;
END IF;

请参阅:

• PL/pgSQL检查行是否存在

- Erwin Brandstetter

Answer 2

对于ROW类型的NULL测试是特殊的：

postgres=# SELECT r, r IS NULL AS "is null", r IS NOT NULL AS "is not null" 
              FROM (VALUES(NULL,NULL),
                          (10, NULL), 
                          (10,10)) r ;
    r    | is null  | is not null 
---------+----------+--------------
 (,)     | t        | f
 (10,)   | f        | f
 (10,10) | f        | t
 (3 rows)

NOT NULL 只在所有字段都不为空时返回 true。

- Pavel Stehule

Answer 3

根据你的代码，你想通过将电子邮件地址插入表格来注册该地址，但前提是该地址尚未被注册并且提供了密码。首先，你应该更改表格定义以反映这些要求：

CREATE TABLE email ( 
    id        serial PRIMARY KEY,
    addr      varchar(50) UNIQUE NOT NULL, 
    passw     varchar(50) NOT NULL,
    person_id integer
);

addr上的UNIQUE约束意味着PG不会允许重复的电子邮件地址，因此您无需进行测试。相反，在进行插入时应检查唯一性冲突。对于该函数，建议您传入电子邮件地址和密码，而不是将业务逻辑放在函数内部。这样，函数具有较少的依赖关系，并且可以更轻松地在其他上下文中重复使用（例如通过 Web 应用程序的其他方式注册电子邮件地址）。将函数设置为 STRICT 可确保 pwd 不为 null，因此可以节省另一个测试步骤。

CREATE OR REPLACE FUNCTION registration(eml text, pwd text) RETURNS integer AS $rL$
DECLARE
    emid integer;
BEGIN
    INSERT INTO email (addr, passw) VALUES (eml, pwd) RETURNING id INTO emid;
    RETURN emid;
EXCEPTION
    WHEN unique_violation THEN
        RAISE 'Email address % already registered', eml;
        RETURN NULL;
END;
$rL$ LANGUAGE plpgsql STRICT;

- Patrick

Answer 4

我遇到了同样的问题，并通过将其转换为::text（arr[1]::text不为空）来解决，在访问类似于此的复合/记录类型中的某些数组时进行SQL选择：

select
  arr,
  arr[1]       is not null  as nn,
  arr[1]::text              as as_txt,
  arr[1]::text is not null  as as_txt_nn
from ...  -- "...": some composite type and an array type for it must exist

row|  arr         nn    as_txt    as_txt_nn
===|  -------     ----- ------    ---------
1  |  {(1,a)}     true  (1,a)     true
2  |  {(1,NULL)}  false (1,NULL)  true
3  |  {NULL}      false <NULL>    false

-- hint: "<NULL>" is the null value representation of 
--                your sql execution environment

所以，as_txt_nn条件正确地检查了与问题相关的第二行和第三行之间的区别，如果第一个数组复合为空或未给出。

如先前的帖子中提到的那样，nn条件的行为方式是，仅当所有复合列都不为空时才返回true。

它也适用于PGPLSQL函数。

- Andreas Covidiot

Answer 5

你只想测试该电子邮件存在的行是否存在。

这可以通过一个EXISTS子查询表达式轻松实现。

IF EXISTS(SELECT 1 FROM email WHERE email_email = eml) THEN
   RAISE EXCEPTION 'email address, %, already registered.', eml;
END IF;

特殊变量FOUND也可以使用，但当您想要使用找到行中的某些字段时，它具有更多的价值。

通常情况下，<row-type> IS [ NOT ] [ DISTINCT FROM ] NULL具有特殊规则，并不总是相互反转（如@Pavel所指出的）；有三种不同的方法来测试某种未知状态：

SELECT r,
  r IS NULL AS "is null",
  r IS NOT NULL AS "is not null",
  r IS DISTINCT FROM NULL AS "is distinct from null"
FROM (
  VALUES
    (ROW(10::int, 10::int)),
    (ROW(10::int, NULL::int)),
    (ROW(NULL::int, NULL::int)),
    (NULL)
) AS s(r);

-- R            IS NULL     IS NOT NULL     IS DISTINCT FROM NULL
-----------------------------------------------------------------
-- '(10,10)'    'f'         't'             't'
-- '(10,)'      'f'         'f'             't'
-- '(,)'        't'         'f'             't'
-- NULL         't'         'f'             'f'

SQLFiddle

注意： 如果表达式是行值，那么当行表达式本身为 null或全部字段都为 null时，IS NULL的结果为 true，而当行表达式本身非空且所有字段都非空时，IS NOT NULL的结果为 true。由于这种行为，针对行值表达式，IS NULL和IS NOT NULL并不总是返回相反的结果，即一个包含NULL和非空值的行值表达式将对两个测试都返回 false。 这个定义符合 SQL 标准，并且与 PostgreSQL 8.2 之前版本存在不一致的行为不同。

此外，在使用复合类型而不是行构造函数时，操作符的处理也有所改变：

注意: SQL规范要求对行进行比较，如果结果取决于比较两个NULL值或一个NULL和一个非NULL值，则返回NULL。PostgreSQL仅在比较两个行构造函数的结果或将行构造函数与子查询的输出进行比较（如第9.22节）时才执行此操作。在其他上下文中¹，比较两个复合类型值时，认为两个NULL字段值相等，并且NULL被认为比非NULL大。这是为了具有一致的复合类型排序和索引行为。

¹虽然我找不到任何按照这种方式工作的查询。

- pozs

---

首先：在不使用查询结果的情况下，不应在此处使用“IF EXISTS（SELECT * ...”；而应改用“PERFORM”。其次：你的示例看起来与 Pavel Stehule 在你之前几个小时发布的内容非常相似，但也许这只是我的错觉... - Patrick

@Patrick 在这种情况下不行，EXISTS(PERFORM ...) 不起作用，因为 EXISTS 消耗了 select 的结果。2: 我只是想强调为什么会发生这种情况（即此定义符合 SQL 标准）-- 主要是因为可能有人对此也感兴趣（例如我自己：我以前没有遇到过这种情况，所以我测试了一下我能做的）。 - pozs

EXISTS(PERFORM ... 从不起作用，但 PERFORM * ...; IF FOUND THEN ... 是唯一被接受的方式（[http://www.postgresql.org/docs/9.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT]）来测试数据的存在。 - Patrick