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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
