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

<row-type> IS NOT NULL

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.

You could invert your test expression:

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

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

See:

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

Leaves a corner case for tables that allow all-NULL rows, though.

Better solutions

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

PERFORM FROM email WHERE email_email = eml;

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

Since we are not actually interested in the returned row, replace SELECT with PERFORM to discard the result. Either sets the special variable FOUND accordingly.
And the SELECT list (or PERFROM list, really) can be empty as only the existence of a row matters.

Simpler, yet, use EXISTS:

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

See:

• PL/pgSQL checking if a row exists

Answer 2

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.