Home > mailing lists

Two columns with same name in subselect--any way to SELECT without *? - Mailing list pgsql-general

From	Ken Tanzer
Subject	Two columns with same name in subselect--any way to SELECT without *?
Date	January 7, 2018 10:46:01
Msg-id	CAD3a31UikwBZwLwAT-cxNDEFOvhHx2RnVT1RCUTR08mA+bpMRw@mail.gmail.com Whole thread Raw
Responses	Re: Two columns with same name in subselect--any way to SELECTwithout *?
List	pgsql-general

Tree view

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!