postgresql - 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

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