Re: [SQL] SELECT multiple tables with same fields

"Dipl.-Ing. Thomas Schallar" <T.Schallar@AVALON.at> writes:
> Before I started programming with Postgres I've done my stuff with
> Microsoft Visual Basic and Access. Access has the lovely feature, that
> doubly selected column names are automatically renamed to
> <tablename>.<columname> (or <tablename>_<columname>? I can't remember at
> the moment; sorry!) so everything works fine.

Hmm.  That is arguably a violation of SQL92: the spec says
        9) Case:
           a) If the i-th <derived column> in the <select list> specifies             an <as clause> that contains a
<columnname> C, then the             <column name> of the i-th column of the result is C.
 
           b) If the i-th <derived column> in the <select list> does not             specify an <as clause> and the
<valueexpression> of that             <derived column> is a single <column reference>, then the             <column
name>of the i-th column of the result is C.
 
    ["C" here apparently refers to the <column name> within     the <column reference> --- tgl]
           c) Otherwise, the <column name> of the i-th column of the <query             specification> is
implementation-dependentand different             from the <column name> of any column, other than itself, of
 a table referenced by any <table reference> contained in the             SQL-statement.
 

So, it appears to me that an implementation has flexibility about the
column name to assign to an expression result, but none about the name
to assign to a simple variable reference.

Which is too bad, because I agree that assigning nonduplicate column
names would be more useful behavior...
        regards, tom lane