Text equality (including null values) in Postgres plpgsql stored function

equals, null, plpgsql, postgresql, sql

Solution

I think you want to use `is not distinct from`:

For non-null inputs, `IS DISTINCT FROM` is the same as the `<>` operator. However, if both inputs are null it returns false, and if only one input is null it returns true. Similarly, `IS NOT DISTINCT FROM` is identical to `=` for non-null inputs, but it returns true when both inputs are null, and false when only one input is null.

Essentially, `A is not distinct from B` is like `A = B` but it treats NULLs as "equal" (i.e. it behaves like most SQL newcomers think `=` should). For example, consider a simple function like this:

create function f(text,text) returns text as $$
begin
    if $1 is distinct from $2 then
        return '!=';
    end if;
    return '==';
end $$
language plpgsql;

That will give you results like this:

=> select f(null, null) as "1"
          f(null, '') as "2",
          f('', '') as "3",
          f('pancakes','pancakes') as "4",
          f('pancakes', null) as "5",
          f('pancakes', 'house') as "6";
 1  | 2  | 3  | 4  | 5  | 6  
----+----+----+----+----+----
 == | != | == | == | != | !=

So something like this is what you're looking for:

if (name1In is not distinct from subjectList.name1) and ...

Problem

I have a stored function which is supposed to compare three text values for equality. Some of these text values may be null, and if they are, then the comparison should return a false value. ``` CREATE OR REPLACE FUNCTION "subject_check_if_subjectName_exists"(name1IN text, name2IN text, name3IN text, name4IN text) returns boolean as $$ declare results boolean; subjectList record; begin results = false; for subjectList in select name1, name2, name3, name4 from subject loop if (name1In = subjectList.name1) and (name2In = subjectList.name2) and (name3In = subjectList.name3) and (name4In = subjectList.name4) then results = true; EXIT; -- exit out of loop end if; end loop; return results; end; $$ language 'plpgsql'; ``` Of both name4IN and subjectList.name4 are null, and all the other values are equal, the function doesn't return a true value - which it should. How can I compare these text values even if they are null (null = null should return true)?

Original source