Postgres returned row fails not null check

Question

I'd really like to know why the IF (previous_foo IS NOT NULL) condition fails below, given that changing this line to IF (previous_foo.total IS NOT NULL) evaluates true.

CREATE OR REPLACE FUNCTION foo_total(the_bar bar)
 RETURNS numeric
 LANGUAGE plpgsql
 STABLE
AS $$
      DECALRE
        previous_foo foo;
      BEGIN
        previous_foo := foo_previous(the_bar);

        IF (previous_foo IS NOT NULL) THEN -- fails
        -- IF (previous_foo.total IS NOT NULL) -- works
          return previous_foo.total;
        END IF;
        RETURN NULL;
      END;
$$;

So how can previous_foo be null, yet previous_foo.total not be null :/

Previous foo is doing something like this FYI

create or replace function previous_foo(the_bar bar) returns foo
    LANGUAGE sql STABLE
    AS $$
      SELECT foo.* FROM foo 
        WHERE -- conditions;
$$;

Laurenz Albe · Accepted Answer · 2017-10-26 23:07:10Z

2

This is conforming to the documentation and the SQL standard:

If the expression is row-valued, then IS NULL is true when the row expression itself is null or when all the row's fields are null, while IS NOT NULL is true when the row expression itself is non-null and all the row's fields are non-null.

Must be that some field in the composite is NULL.

This is not the only strange ruling of the standard committee when it comes to NULL…

edited Oct 26, 2017 at 23:07

answered Oct 26, 2017 at 22:49

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Over a year ago

Thanks, Yes ok this makes some "sense". Some of the fields in that row are null.

Collectives™ on Stack Overflow

Postgres returned row fails not null check

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related