Re: Same column names in a subresult table

On 14/05/21, Durumdara (durumdara(at)gmail(dot)com) wrote:
> ...
> midqry1 as (
> select coalesce(XDate , '0001-01-01'), * from prevqry
> ),
> midqry2 as (
> select ArtID, max(XDate) as Max_XDate from midqry1
> where acq = True
> group by ArtID
> )
> ...
>
> Result: ERROR: column reference "XDate" is ambiguous

test=> create table stuff (xdate date, artid integer, acq boolean);
test=> insert into stuff values
(date'2020-01-01', 1, true)
,(date'2020-01-02', 1, true)
,(date'2020-01-03', 1, false)
,(date'2020-01-01', 2, true)
;

test=> \e
with prevquery as (
select * from stuff
)
,midqry1 as (
select
coalesce (xdate, date'0001-01-01') as xdate, artid, acq
from
prevquery
)
,midqry2 as (
select
artid, max(midqry1.xdate) as max_xdate
from
midqry1
where
acq is true
group by
artid
)
select * from midqry2;

artid | max_xdate
-------+------------
2 | 2020-01-01
1 | 2020-01-02
(2 rows)

At present your midqry1 has two columns named xdate. Otherwise it is a good
idea to qualify the column by the temporary table named by each WITH.

Although you've only shown a snippet of your query here it would be pretty
simple to aggregate those two WITHs or put them into the main query. This is
likely to be a lot faster if you are querying a lot of data.

Rory