On Sat, Jan 6, 2018 at 9:16 PM, Ken Tanzer <ken.tan...@gmail.com> wrote:
> > > On Sat, Jan 6, 2018 at 9:10 PM, Adrian Klaver <adrian.kla...@aklaver.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
