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.

> 
> I found myself wanting to specify the columns by position, similar to 
> ORDER or GROUP. (*.1, foo.1 or something like that), but that's probably 
> wishful thinking!
> 
> Cheers,
> 
> Ken
> 
> 
> 
> -- 
> AGENCY Software
> A Free Software data system
> By and for non-profits
> /http://agency-software.org//
> /https://demo.agency-software.org/client/
> ken.tanzer@agency-software.org <mailto:ken.tanzer@agency-software.org>
> (253) 245-3801
> 
> Subscribe to the mailing list 
> <mailto:agency-general-request@lists.sourceforge.net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.


-- 
Adrian Klaver
adrian.klaver@aklaver.com