Why is IS NOT NULL false when checking a row type?

Question

I have a function registration(), which is supposed to add a row to a table under certain circumstances. I've included a snippet of code and output from a call.

If select * returns a non-empty table row (which it does according to the RAISE NOTICE) I want to raise the exception and not add the row. The example seems to show that rowt is not null, and yet rowt IS NOT NULL returns f (and the exception is not raised).

I hope this is something minor I'm not seeing.

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;

Output:

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;

Answer 1

Test on NULL for ROW type is specific:

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)

So NOT NULL returns true only when all fields are not null.

Answer 2

I had the same problem and solved it by casting to ::text (arr[1]::text is not null) within an SQL select accessing some array within a composite/record type similar to this:

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

So as_txt_nn condition properly checks for the problem to differentiate between row 2 and 3 related to the question, if the first array composite is null or given.

The nn condition behaves (as mentioned in previous posts) in a way, that it only returns true, if ALL composite columns are not null.

It should work for PGPLSQL functions as well.

Answer 3

As @Pavel provided, the check <row-type> IS NOT NULL doesn't work like you seem to expect. It returns TRUE if (and only if) every single column is NOT NULL.

Test the special variable FOUND instead (like @Mike commented):

CREATE OR REPLACE FUNCTION registration(wr text)
  RETURNS integer AS
$rL$
    ...

    SELECT * INTO rowt FROM email WHERE email_email = eml;

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

    ...
$rL$ LANGUAGE plpgsql;

Or you could invert your expression in the test.

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

Any existing row you would find contains at least one column that is NOT NULL, therefore rowt IS NULL only returns TRUE if nothing is found.

Related answers with more details:

• IS NOT NULL test for a record does not return TRUE when variable is set
• NOT NULL constraint over a set of columns

Answer 4

You only want to test, whether the row with that email exists.

This can be achieved simply, with an EXISTS sub-query expression:

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

The special variable FOUND could work too, but it's have more value, when you want to use some fields from the found row.

In general, <row-type> IS [ NOT ] [ DISTINCT FROM ] NULL has special rules, and does not always inverse of each other (like @Pavel noted); there are 3 distinct method to test against some kind of unknown state:

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

Note: 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, i.e., a row-valued expression that contains both NULL and non-null values will return false for both tests. This definition conforms to the SQL standard, and is a change from the inconsistent behavior exhibited by PostgreSQL versions prior to 8.2.

Also, there is some change in the handling of operators, when someone uses composite types, instead of row constructors:

Note: The SQL specification requires row-wise comparison to return NULL if the result depends on comparing two NULL values or a NULL and a non-NULL. PostgreSQL does this only when comparing the results of two row constructors or comparing a row constructor to the output of a subquery (as in Section 9.22). In other contexts¹ where two composite-type values are compared, two NULL field values are considered equal, and a NULL is considered larger than a non-NULL. This is necessary in order to have consistent sorting and indexing behavior for composite types.

¹ though I could not find any query, which works that way.

Answer 5

From your code it follows that you want to register an email address by inserting it in a table, but only if the email address isn't already registered and a password is supplied. For starters, you should change your table definition to reflect those requirements:

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

The UNIQUE constraint on addr means that PG will not allow duplicate email addresses so you don't have to test for that. You should instead test for a unique violation when doing the insert.

For the function I suggest you pass in the email address and password, instead of putting the business logic inside the function. Like this the function has fewer dependencies and can be re-used in other contexts more easily (such as registering an email address via some other means via your web app). Making the function STRICT ensures that pwd is not null so that saves you another test.

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;