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

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.