From: Stephen Cook <sclists@gmail.com>
To: S G <sgennaria2@gmail.com>
Cc: Rob Sargent <robjsargent@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: distinguishing identical columns after joins
Date: Tue, 01 Mar 2011 18:37:46 -0500
Message-ID: <4D6D834A.50405@gmail.com> (raw)
In-Reply-To: <AANLkTimzBrzzdKa4=k9VnXLVNzw2U_PzOSbRzhCZJhn=@mail.gmail.com>
References: <AANLkTinSR8Wuago7HdATetEiXQLU8fjbnm2tX5n1RGHG@mail.gmail.com>
<4D6D4EC2.70306@gmail.com>
<AANLkTimzBrzzdKa4=k9VnXLVNzw2U_PzOSbRzhCZJhn=@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sclists@gmail.com, sgennaria2@gmail.com, robjsargent@gmail.com
Subject: Re: distinguishing identical columns after joins
In-Reply-To: <4D6D834A.50405@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox