I don't understand why this result is possible. Can someone explain why the first assert returns true and the second false? generate_token() is a function that generates a random bytea and inserts it into a certain table, then the inserted row is returned. The function itself is working fine. Running through a .pgsql script.

DO $$
    test record;

select * into test from generate_token('test');

--returns true
assert test.token IS NOT NULL;
--returns false, which breaks.
assert test IS NOT NULL;
  • Disclosing the exact signature of the function generate_token() (including the return type) would be instrumental for the question. And always your version of Postgres. Nov 21, 2020 at 15:33

1 Answer 1


... then the inserted row is returned.

It's safe to assume there are multiple fields in that returned row. If at least one additional field is NULL, then the row value is neither NULL nor NOT NULL. The manual:

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. Because of this behavior, IS NULL and IS NOT NULL do not always return inverse results for row-valued expressions; in particular, a row-valued expression that contains both null and non-null fields will return false for both tests.

Bold emphasis mine.

db<>fiddle here


  • Thats a really weird interaction... In OO it is safe to assume that an object's field can be null while the object itself is not.. Living and learning... Thanks!
    – rdnobrega
    Nov 21, 2020 at 16:19
  • @rdnobrega Yes, the SQL standard committee must have smoked something weird when they decreed that. Nov 23, 2020 at 0:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.