Re: Two columns with same name in subselect--any way to SELECT without *?

On Sat, Jan 6, 2018 at 9:10 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 01/06/2018 08:46 PM, Ken Tanzer wrote:
>
>> Hi. You can have multiple columns with the same name, and use it as a
>> subselect, like this silly example:
>>
>> SELECT 'a' AS my_col,'b' AS my_col,'foo' AS other;
>> SELECT * FROM (SELECT 'a' AS my_col,'b' AS my_col,'foo' AS other) foo;
>>
>> But is there any way to select either of those columns without taking all
>> the fields with *?
>>
>> SELECT my_col,other FROM (select 'a' AS my_col,'b' AS my_col,'foo' AS
>> other) foo;
>> ERROR: column reference "my_col" is ambiguous
>>
>> I suspect there isn't, but just wondering if there's some way I'm not
>> aware of.
>>
>
> ?:
> SELECT bar.my_col, foo.my_col FROM (SELECT 'a' AS my_col) as bar , (select
> 'b' AS my_col,'foo' AS other) foo;
>
> my_col | my_col
> --------+--------
> a | b
>
> Though I would think this would just be pushing the point where you get
> confused what my_col is really pointing to down the road.
>

Thanks Adrian, but I was really wondering about the case where the two
columns are already in a single result set. I came across this issue
accidentally, and it's not causing any problems. Just trying to understand
the possibilities/limitations for future reference.

Cheers,
Ken