Re: CTE containing ambiguous columns

On Fri, Nov 13, 2009 at 8:32 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
>
> Robert Haas wrote:
>>
>> create table animals (id serial primary key, name varchar not null);
>>
>>
>>
>
> ...
>>
>> with beings as (select * from animals a1, animals a2) select * from
>> beings where id = 1;
>>
>>
>>
>
> "Doctor, it hurts when I do this."
> "So stop doing that."
>
> Can't you disambiguate it using a column list on beings?

Sure, after I figured out what the real problem was. Maybe I'm a
dope, but when I get an error cursor pointed at an ambiguous column
reference, my thought is "oh, I need to qualify that reference" - not
"oh, some completely unrelated part of the query has an *-expansion
that contains duplicate columns". Something like:

HINT: <alias> contains multiple columns named <colname>

...would help a lot. I don't feel strongly about it, I just thought
it was confusing.

...Robert