Re: How to eliminate extra "NOT EXISTS"-query here? - Mailing list pgsql-general

From hector vass
Subject Re: How to eliminate extra "NOT EXISTS"-query here?
Date
Msg-id CAJJx+iX0LEL1vjNC-YbaEzQ6rApBsz7x7ztVzw_6RLbYOcSapw@mail.gmail.com
Whole thread Raw
In response to Re: How to eliminate extra "NOT EXISTS"-query here?  (Dominique Devienne <ddevienne@gmail.com>)
List pgsql-general
Not equivalent to the use of NOT ARRAY and entirely possible I have misunderstood the requirement ...do you have some more test cases the non array solution does not work for

Regards
Hector Vass
07773 352559


On Mon, Nov 27, 2023 at 9:29 AM Dominique Devienne <ddevienne@gmail.com> wrote:
On Sat, Nov 25, 2023 at 5:53 PM hector vass <hector.vass@gmail.com> wrote:
Not sure you need to use array why not simple table joins, so a table with your criteria x y z t joined to stuff to give you candidates that do match, then left join with coalesce to add the 'd' 

select

--a.id,b.test_id,

coalesce(a.id,b.test_id) as finalresult

from test a

left join (

select

test_id

from stuff a

inner join (values ('x'),('y'),('z'),('t')) b (v) using(v)

group by 1

)b on(a.id=b.test_id);


Hi Hector. Hopefully this is not a stupid question...

How is that equivalent from the `NOT ARRAY ... <@ ...` though?
The inner-join-distinct above will return test_id's on any match, but you
can't know if all array values are matches. Which is different from

> Is the first array contained by the second

from the <@ operator, no?
I'm unfamiliar with these operators, so am I missing something?
Just trying to understand the logic here. Thanks, --DD

pgsql-general by date:

Previous
From: Dominique Devienne
Date:
Subject: Get back the number of columns of a result-set prior to JSON aggregation
Next
From: Roman Šindelář
Date:
Subject: ALTER SUBSCRIPTION ... REFRESH cannot be executed from a function