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

On 03/01/2011 06:00 PM, Rob Sargent wrote:
>
> 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?:)
>
I don't know if someone has come up with a workaround, but to begin with
note that the docs specify that when using the * "it is not possible to
specify new names with AS; the output column names will be the same as
the table columns' names."

Off the cuff, a possible workaround would be to create multiple views of
your table that rename the columns, i.e.

CREATE VIEW vw_a_t1 AS
SELECT
col1 AS a_col1, col2 AS a_col2, …
FROM
t1;

CREATE VIEW vw_b_t1 AS
SELECT
col1 AS b_col1, col2 AS b_col2, …
FROM
t1;

Then you would do your select as

SELECT *
FROM vw_a_t1 JOIN vw_b_t1 ON a_col1 = b_col1;

If you were often self-joining the table 3 or more times, you would
obviously have to create views vw_c_t1, vw_d_t1, etc. If you need to do
this for several tables, you might be able to create a function to
create the views. The function would take a table name and the desired
prefix as parameters and programatically construct, then EXECUTE the
CREATE VIEW statement.

--Lee

-- 
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center