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

On Sat, Jan 6, 2018 at 9:16 PM, Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> wrote:

>
>
> 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
>
> So having thought about this a little more, it seems like once you create
a result set with identically-named columns, those columns are effectively
crippled. In that they can be viewed (via SELECT *), but not referenced,
used or acted upon in any way. Still just wanting to confirm this is/is
not the case. Thanks!

Ken