Skip site navigation (1) Skip section navigation (2)

Re: distinguishing identical columns after joins

From: Stephen Cook <sclists(at)gmail(dot)com>
To: S G <sgennaria2(at)gmail(dot)com>
Cc: Rob Sargent <robjsargent(at)gmail(dot)com>, pgsql-sql(at)postgresql(dot)org
Subject: Re: distinguishing identical columns after joins
Date: 2011-03-01 23:37:46
Message-ID: 4D6D834A.50405@gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
In times like these, I usually write a query using 
information_schema.columns to generate the column list:


SELECT  ordinal_position,
         1 AS table_instance,
         'a.' || column_name || ' AS ' || column_name || '_a,'
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME = 'your_table_here'
UNION ALL
SELECT  ordinal_position,
         2 AS table_instance,
         'b.' || column_name || ' AS ' || column_name || '_b,'
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME = 'your_table_here'
ORDER BY table_instance,
         ordinal_position;


Or something along those lines, and copy-and-paste the results into the 
query. It's quicker than typing them all out once you hit a certain 
number of columns, and certainly less typo-prone.

It's not the shortcut you were thinking of but it works.


On 3/1/2011 5:13 PM, S G wrote:
> 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.

In response to

pgsql-sql by date

Next:From: Lee HachadoorianDate: 2011-03-01 23:44:14
Subject: Re: distinguishing identical columns after joins
Previous:From: Rob SargentDate: 2011-03-01 23:00:39
Subject: Re: distinguishing identical columns after joins

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group