Is Not Null Test for a Record Does Not Return True When Variable Is Set

IS NOT NULL test for a record does not return TRUE when variable is set

I see two possible reasons, why ...

Neither of these raises appear in my messages log

Not logged

Firstly, a NOTICE is not normally written to the database log with default settings. The manual:

log_min_messages (enum)
Controls which message levels are written to the server log. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC. (...)

The default is WARNING. Note that LOG has a different rank here than in client_min_messages.

Bold emphasis mine. Also note the different default (NOTICE) for client_min_messages (preceding item in the manual).

Invalid test

Secondly, consider how a row expression is evaluated. A test row_variable IS NULL returns TRUE if (and only if) every single element is NULL. Consider:

SELECT (1, NULL) IS NULL     AS a  -- FALSE
     , (1, NULL) IS NOT NULL AS b  -- also FALSE!

Both expressions return FALSE. In other words, a row (or record) variable (1, NULL) is neither NULL, nor is it NOT NULL. Therefore, both of your tests fail.

sqlfiddle with more details

NOT NULL constraint over a set of columns

You can even assign a record variable with NULL (rec := NULL), which results in every element being NULL - if the type is a well-known row type. Otherwise, we are dealing with an anonymous record and the structure is undefined and you cannot access elements to begin with. But that's not the case with a rowtype like in your example (which is always well-known).

Solution: `FOUND`

What's the correct way to test if you received a row from a SELECT * INTO?

You have to consider that the row could be NULL, even if it was assigned. The query could very well have returned a bunch of NULL values (if the table definition in your query allows NULL values).

There is a simple and secure approach. Use GET DIAGNOSTICS or, where applicable, the special variable FOUND:

SELECT * FROM my_table WHERE owner_id = 6 INTO my_var;

IF NOT FOUND THEN
   RAISE NOTICE 'Query did not return a row!';
END IF;

Details in the manual.

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

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;

Test for NULL value in a variable that may not be set

$result = array_key_exists('varname', get_defined_vars());

Check a record IS NOT NULL in plsql

As far as I know, it's not possible. Checking the PRIMARY KEY or a NOT NULL column should be sufficient though.

You can check for v_record.row_id IS NULL.

Your function would throw a NO_DATA_FOUND exception though, when no record is found.

How to check if a value is not null and not empty string in JS

If you truly want to confirm that a variable is not null and not an empty string specifically, you would write:

if(data !== null && data !== '') {
   // do something
}

Notice that I changed your code to check for type equality (!==|===).

If, however you just want to make sure, that a code will run only for "reasonable" values, then you can, as others have stated already, write:

if (data) {
  // do something
}

Since, in javascript, both null values, and empty strings, equals to false (i.e. null == false).

The difference between those 2 parts of code is that, for the first one, every value that is not specifically null or an empty string, will enter the if. But, on the second one, every true-ish value will enter the if: false, 0, null, undefined and empty strings, would not.

Check if returned value is not null and if so assign it, in one line, with one method call

Java lacks coalesce operator, so your code with an explicit temporary is your best choice for an assignment with a single call.

You can use the result variable as your temporary, like this:

dinner = ((dinner = cage.getChicken()) != null) ? dinner : getFreeRangeChicken();

This, however, is hard to read.

Best way to check for empty or null value

The expression stringexpression = '' yields:

TRUE .. for '' (or for any string consisting of only spaces with the data type char(n))

NULL .. for NULL

FALSE .. for anything else

So to check for: "stringexpression is either NULL or empty":

(stringexpression = '') IS NOT FALSE

Or the reverse approach (may be easier to read):

(stringexpression <> '') IS NOT TRUE

Works for any character type including char(n). The manual about comparison operators.

Or use your original expression without trim(), which is costly noise for char(n) (see below), or incorrect for other character types: strings consisting of only spaces would pass as empty string.

coalesce(stringexpression, '') = ''

But the expressions at the top are faster.

Asserting the opposite is even simpler: "stringexpression is neither NULL nor empty":

stringexpression <> ''

About `char(n)`

This is about the data type char(n), short for: character(n). (char / character are short for char(1) / character(1).) Its use is discouraged in Postgres:

In most situations text or character varying should be used instead.

Do not confuse char(n) with other, useful, character types varchar(n), varchar, text or "char" (with double-quotes).

In char(n) an empty string is not different from any other string consisting of only spaces. All of these are folded to n spaces in char(n) per definition of the type. It follows logically that the above expressions work for char(n) as well - just as much as these (which wouldn't work for other character types):

coalesce(stringexpression, '  ') = '  '
coalesce(stringexpression, '') = '       '

Demo

Empty string equals any string of spaces when cast to char(n):

SELECT ''::char(5) = ''::char(5)     AS eq1
     , ''::char(5) = '  '::char(5)   AS eq2
     , ''::char(5) = '    '::char(5) AS eq3;

Result:

 eq1 | eq2 | eq3
 ----+-----+----
 t   | t   | t

Test for "null or empty string" with char(n):

SELECT stringexpression 
     , stringexpression = ''                   AS base_test
     , (stringexpression = '')  IS NOT FALSE   AS test1
     , (stringexpression <> '') IS NOT TRUE    AS test2
     , coalesce(stringexpression, '') = ''     AS coalesce1
     , coalesce(stringexpression, '  ') = '  ' AS coalesce2
     , coalesce(stringexpression, '') = '  '   AS coalesce3
FROM  (
   VALUES
     ('foo'::char(5))
   , ('')
   , ('   ')                -- not different from '' in char(n)
   , (NULL)
   ) sub(stringexpression);

Result:


 stringexpression | base_test | test1 | test2 | coalesce1 | coalesce2 | coalesce3 
------------------+-----------+-------+-------+-----------+-----------+-----------
 foo              | f         | f     | f     | f         | f         | f
                  | t         | t     | t     | t         | t         | t
                  | t         | t     | t     | t         | t         | t
 null             | null      | t     | t     | t         | t         | t

Test for "null or empty string" with text:

SELECT stringexpression 
     , stringexpression = ''                   AS base_test
     , (stringexpression = '')  IS NOT FALSE   AS test1
     , (stringexpression <> '') IS NOT TRUE    AS test2
     , coalesce(stringexpression, '') = ''     AS coalesce1
     , coalesce(stringexpression, '  ') = '  ' AS coalesce2
     , coalesce(stringexpression, '') = '  '   AS coalesce3
FROM  (
   VALUES
     ('foo'::text)
   , ('')
   , ('   ')                -- different from '' in a sane character types
   , (NULL)
   ) sub(stringexpression);

Result:


 stringexpression | base_test | test1 | test2 | coalesce1 | coalesce2 | coalesce3 
------------------+-----------+-------+-------+-----------+-----------+-----------
 foo              | f         | f     | f     | f         | f         | f
                  | t         | t     | t     | t         | f         | f
                  | f         | f     | f     | f         | f         | f
 null             | null      | t     | t     | t         | t         | f

db<>fiddle here

_{Old sqlfiddle}

Any downsides of using data type "text" for storing strings?

Check if a Postgres composite field is null/empty

IS NULL and IS NOT NULL work for complex types too, so these two should be appropriate:

select * from bla where recipient is not null
select * from bla where recipient is null

How to check for Is not Null And Is not Empty string in SQL server?

If you only want to match "" as an empty string

WHERE DATALENGTH(COLUMN) > 0

If you want to count any string consisting entirely of spaces as empty

WHERE COLUMN <> ''

Both of these will not return NULL values when used in a WHERE clause. As NULL will evaluate as UNKNOWN for these rather than TRUE.

CREATE TABLE T 
  ( 
     C VARCHAR(10) 
  ); 

INSERT INTO T 
VALUES      ('A'), 
            (''),
            ('    '), 
            (NULL); 

SELECT * 
FROM   T 
WHERE  C <> ''

Returns just the single row A. I.e. The rows with NULL or an empty string or a string consisting entirely of spaces are all excluded by this query.

SQL Fiddle

Is Not Null Test for a Record Does Not Return True When Variable Is Set