Thread: Same column names in a subresult table

On 14/05/21, Durumdara (durumdara@gmail.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

Is there any way to suppress the original field?

Or say to PGSQL to skip the first XDate field?

Like select t.* (EXCEPT XDate) from t

Or can I reference them by the order?

Ok, I can solve this problem by renaming the new first XDate, but I want to know is there any solution to this problem?