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

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