Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Ken Tanzer
Date:
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
(253) 245-3801

learn more about AGENCY or
follow the discussion.

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

From
Adrian Klaver
Date:
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

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

From
Ken Tanzer
Date:


On Sat, Jan 6, 2018 at 9:10 PM, Adrian Klaver <adrian.klaver@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

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

From
Ken Tanzer
Date:


On Sat, Jan 6, 2018 at 9:16 PM, Ken Tanzer <ken.tanzer@gmail.com> wrote:


On Sat, Jan 6, 2018 at 9:10 PM, Adrian Klaver <adrian.klaver@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

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

From
"David G. Johnston"
Date:
On Saturday, January 6, 2018, Ken Tanzer <ken.tanzer@gmail.com> wrote:
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!

I'm not really following the intent of the question but if your client API allows for column references by position instead of name you can still distinguish between them, too.

In subqueries or CREATE TABLE AS, no, the system, is going to force you to differentiate them since all SQL references are by name.

David J.