Re: Postgres views cannot use both union and join/where

2021-10-20 Thread David G. Johnston
On Wed, Oct 20, 2021 at 6:58 AM Tom Lane wrote: > "David G. Johnston" writes: > > On Tuesday, October 19, 2021, Michael Lewis wrote: > >> On Tue, Oct 19, 2021 at 3:48 PM Mithran Kulasekaran < > >> mithranakulaseka...@gmail.com> wrote: > >>> create view template_view (id, name, description, is_

Re: Postgres views cannot use both union and join/where

2021-10-20 Thread Tom Lane
"David G. Johnston" writes: > On Tuesday, October 19, 2021, Michael Lewis wrote: >> On Tue, Oct 19, 2021 at 3:48 PM Mithran Kulasekaran < >> mithranakulaseka...@gmail.com> wrote: >>> create view template_view (id, name, description, is_staged) as >>> select t.id,t.name, t.description, false as i

Re: Postgres views cannot use both union and join/where

2021-10-19 Thread David G. Johnston
On Tuesday, October 19, 2021, Michael Lewis wrote: > On Tue, Oct 19, 2021 at 3:48 PM Mithran Kulasekaran < > mithranakulaseka...@gmail.com> wrote: > >> create view template_view (id, name, description, is_staged) as >> select t.id,t.name, t.description, false as is_staged >> from template t >>

Re: Postgres views cannot use both union and join/where

2021-10-19 Thread Michael Lewis
On Tue, Oct 19, 2021 at 3:48 PM Mithran Kulasekaran < mithranakulaseka...@gmail.com> wrote: > create view template_view (id, name, description, is_staged) as > select t.id,t.name, t.description, false as is_staged > from template t > left join template_staging ts on t.name = ts.name and

Re: Postgres views cannot use both union and join/where

2021-10-19 Thread Benedict Holland
I thought a union mashed together two queries. The where clause can appear in both. But the execution plan will almost certainly run the first query and the second query. It should throw an error if the types don't match or the number of columns don't match. There are so few use cases for unions t

Re: Postgres views cannot use both union and join/where

2021-10-19 Thread David G. Johnston
On Tue, Oct 19, 2021 at 2:48 PM Mithran Kulasekaran < mithranakulaseka...@gmail.com> wrote: > i think the only problem is when we try to use both union and where/join > the issue starts to happen > I'm unconvinced this is actually an issue based upon what is presented here. All I'm seeing is two

Postgres views cannot use both union and join/where

2021-10-19 Thread Mithran Kulasekaran
Hi, We are trying to use the postgres view to accommodate some of the complex workflow related operations, we perform we saw like using union in a where clause inside a view actually pushed the where clause to both subqueries and we get good performance using the index , but when used in a join it