From: | Dominique Devienne <ddevienne(at)gmail(dot)com> |
---|---|
To: | hector vass <hector(dot)vass(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Andreas Joseph Krogh <andreas(at)visena(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: How to eliminate extra "NOT EXISTS"-query here? |
Date: | 2023-11-27 09:29:46 |
Message-ID: | CAFCRh-_=juDSPqoLcPyXf9FP4DWvq6B2FSEqrZ16sZJH2QJeow@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sat, Nov 25, 2023 at 5:53 PM hector vass <hector(dot)vass(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Dominique Devienne | 2023-11-27 09:33:00 | Re: Emitting JSON to file using COPY TO |
Previous Message | Wirch, Eduard | 2023-11-27 07:17:17 | PostgreSql: Canceled on conflict out to old pivot |