Why is IS NOT NULL false when checking a row type?
<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 PERFORM
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
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.