Re: distinguishing identical columns after joins - Mailing list pgsql-sql

On 03/01/2011 03:13 PM, S G wrote:
> On Tue, Mar 1, 2011 at 2:53 PM, Rob Sargent <robjsargent@gmail.com> wrote:
>>
>>
>> On 03/01/2011 12:47 PM, S G wrote:
>>> This question is particularly geared towards self-joins, but can apply
>>> to any join where the tables involved have any identical column names.
>>>  Aside from explicit column references, is there any way to pull all
>>> columns (*) from each table in a join and quickly append/prepend some
>>> identifier to distinguish them from each other?  For example, table t1
>>> contains columns named col1 and col2:
>>>
>>> SELECT
>>>    *
>>> FROM
>>>    t1 AS a
>>> INNER JOIN
>>>    t1 AS b
>>> ON
>>>    a.col1 = b.col1
>>>
>>> would yield a result set with column names: col1, col2, col1, col2.
>>> I'm looking for something that would automatically rename the columns
>>> like: a_col1, a_col2, b_col1, b_col2.  Does such functionality exist?
>>> It's not such a big deal in this example, but it can be quite tedious
>>> to explicitly reference and rename every single column for such joins
>>> when the tables involved have a very large number of columns.
>>>
>>> I would beg for the same functionality when expanding compound
>>> datatypes.  For example, a compound datatype cd1 exists with fields
>>> named f1 and f2:
>>>
>>> SELECT
>>>    ((value1, value2)::cd1).* AS a
>>>
>>> normally produces a result set with column names: f1, f2.  I'm looking
>>> for something that would produce column names: a_f1, a_f2.
>>>
>>> Thanks!
>>> sg
>>>
>> select a.col1 as a_col1 etc doesn't do it for you?
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
> 
> Rob, what you wrote certainly does work.  But suppose you had to do
> that for a join with 50 columns in each table, and you really needed
> to see all those columns show up in the final result set, and
> furthermore, you needed to be able to identify each one uniquely in
> the final result set.  Explicit renaming works, but it's tedious.
> Call me lazy.  I'm hoping a column-renaming shortcut exists that works
> with the "SELECT *" concept.
> 
> If such a shortcut doesn't exist, I believe it easily could exist
> utilizing the following syntax:
> 
> SELECT
>    (a).* AS a_,
>    (b).* AS b_
> FROM
>    t1 AS a
> INNER JOIN
>    t1 AS b
> ON
>    a.col1 = b.col1
> 
> which currently discards the AS identifiers and defaults to the column
> names as identified in their respective tables.  Though implementing
> this is another issue altogether... I'm just asking if such a shortcut
> already exists.
> 
> Thanks!
> sg

I suspected this was the tack you were taking and would be mildly
surprised if it hasn't been requested before so I suspect some wise soul
will put us in the right direction.

But I still wonder it isn't a receiver/UI issue.  Does your reader know
the meaning of "a_" vs "b_" in a non-trivial self join?  Wouldn't you
rather have the output as a_col1, b_col1, a_col2, b_col2 ... for easy
comparison.  And who can make sense of a 100 column results set anyway?:)