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

[Ken Tanzer <ken.tanzer@xxxxxxxxx>]

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.  

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@xxxxxxxxxxxxxxxxxxx

(253) 245-3801

Subscribe to the mailing list to

learn more about AGENCY or

follow the discussion.