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

Hi David,

Thank you for explaining this - I had not even considered that the
behaviour mandated by the SQL standard would be so unintuitive. At least I
now know how to achieve what I want!

Robert

On Thu, 15 Aug 2024 at 19:30, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> 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.
>
>