Why IS NOT NULL false when checking string type?
I have a function registration()
that, under certain circumstances, should add a row to a table. I have included the code snippet and the output from the call.
If select *
returns a non-empty table row (which is executed according to RAISE NOTICE
) then I want to raise the exception and not add the row. The example shows that rowt
it is not null but rowt IS NOT NULL
returns f
(and no exception is set).
I hope this is something insignificant that I do not see.
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;
source to share
It follows from your code that you want to register an email address by pasting it into a spreadsheet, but only if the email address has not yet been registered and no password has been specified. First, you must change the table definition to reflect these requirements:
CREATE TABLE email (
id serial PRIMARY KEY,
addr varchar(50) UNIQUE NOT NULL,
passw varchar(50) NOT NULL,
person_id integer
);
The limit UNIQUE
on addr
means that the PG will not allow duplicate email addresses, so you don't need to check that. Instead, you should check for a unique violation when performing an insert.
For this function, I suggest you pass in the email and password instead of putting the business logic inside the function. Likewise, the function has fewer dependencies and can be reused in other contexts more easily (for example, register an email address with some other means through your web application). The function execution STRICT
ensures it is pwd
not null, to save 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;
source to share
As @Pavel pointed out , validation <row-type> IS NOT NULL
does not work as you expect. It returns TRUE
if (and only if) every single column is equal NOT NULL
.
Test a custom variable instead FOUND
(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 can 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 find contains at least one column NOT NULL
, so it only rowt IS NULL
returns TRUE
if nothing was found.
Related answers with more details:
source to share
The NULL test for the 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
only returns true when all fields are non-null.
source to share
You only want to check if a string exists with that address.
This can be achieved simply, with a subquery expression EXISTS
:
IF EXISTS(SELECT 1 FROM email WHERE email_email = eml) THEN
RAISE EXCEPTION 'email address, %, already registered.', eml;
END IF;
A custom variable FOUND
can work too, but it is more important if you want to use some of the fields from the found string.
In general, it <row-type> IS [ NOT ] [ DISTINCT FROM ] NULL
has special rules and does not always refer to each other (for example, @Pavel ); there are 3 different methods for testing an 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'
Note: If expression evaluates to a string, thentrue when the string expression itself is null or ** when all fields in the string are null **, andtrue when the string expression itself is nonzero and all fields in the string are not significant. null . Because of this behavior,anddoes not always return backward results for row-evaluated expressions, that is, a row-evaluated expression that contains both valuesand non-null values returns false for both tests. This definition follows the SQL standard and is a change from the inconsistent behavior exhibited by PostgreSQL versions prior to 8.2.
IS NULL
IS NOT NULL
IS NULL
IS NOT NULL
NULL
Also, there are some changes in operator handling where someone is using composite types rather than string constructors:
Note: The SQL specification requires string matching to return
NULL
if the result depends on comparing two valuesNULL
orNULL
and notNULL
. 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 1 where two composite values are compared, the two field valuesNULL
are considered equal, and aNULL
is considered greater than notNULL
. This is necessary to ensure consistent sorting and indexing for composite types.
1 although I haven't been able to find any query that works this way.
source to share
I had the same problem and solved it by casting::text
( arr[1]::text is not null
) in a SQL fetch, which accesses some array as a composite / 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 the condition as_txt_nn
correctly checks that the problem is different between lines 2 and 3 related to the question if the first composite array is null or given.
The condition behaves (as mentioned in previous posts) in such a way that it only returns if ALL compound columns . nn
true
not null
It should work for PGPLSQL functions as well.
source to share