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

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

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.

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;