Re: [SQL] SELECT multiple tables with same fields

"Dipl.-Ing. Thomas Schallar" <T(dot)Schallar(at)AVALON(dot)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 <column name> 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 <value expression> 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-dependent and 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