DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=wWydIp+TjUgcsc0+1AjyBBJy/Aim7JsQVznAJy0+DDN7YrbNbhOyWdO0HwVlpG+KBk
	PnzmifB17UwzIAKoucEQ0LJsjKa8C51tHVpolHumEflEaLOADsSCLKIk/2EpfOhA+YX8
	MsHCKpRBtDxmElHI9SvoyqRHviB/nxf565HaI=
Message-ID: <4D6D834A.50405@gmail.com>
Date: Tue, 01 Mar 2011 18:37:46 -0500
From: Stephen Cook <sclists@gmail.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US;
	rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
To: S G <sgennaria2@gmail.com>
CC: Rob Sargent <robjsargent@gmail.com>, pgsql-sql@postgresql.org
Subject: Re: distinguishing identical columns after joins
References: <AANLkTinSR8Wuago7HdATetEiXQLU8fjbnm2tX5n1RGHG@mail.gmail.com>
	<4D6D4EC2.70306@gmail.com>
	<AANLkTimzBrzzdKa4=k9VnXLVNzw2U_PzOSbRzhCZJhn=@mail.gmail.com>
In-Reply-To: <AANLkTimzBrzzdKa4=k9VnXLVNzw2U_PzOSbRzhCZJhn=@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.