PostgreSQL's record type returning null?

Question

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 $$
DECLARE
    test record;
BEGIN

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

--returns true
assert test.token IS NOT NULL;
--returns false, which breaks.
assert test IS NOT NULL;
END;$$;

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

Related:

• NOT NULL constraint over a set of columns
• (x IS NOT NULL) vs (NOT x IS NULL) in PostgreSQL