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

On Wednesday, August 14, 2024, PG Bug reporting form <noreply(at)postgresql(dot)org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference: 18583
> Logged by: Robert Greig
> Email address: robert(dot)j(dot)greig(at)gmail(dot)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.