Re: BUG #18583: jsonb_populate_record return values cannot be queried correctly in subselects

On Wednesday, August 14, 2024, PG Bug reporting form <noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference:      18583
Logged by:          Robert Greig
Email address:      robert.j.greig@gmail.com
PostgreSQL version: 16.4
Operating system:   Windows
Description:       

create type test_simple_type as (
        f1 varchar(20),
        f2 int
);

where val is not null;

It is not obvious to me why it is not working but I believe this is a defect
and I can't find a workaround.

The “workaround” is to write that test:  not (val is null)

The meaning of is not null for a composite is non-intuitive and usually not what you want - i.e., true only if all fields are also non-null.

The docs do cover this, and there is a pending patch to further expand on how null values are handled in various places in PostgreSQL.

David J.