Thread: SELECT multiple tables with same fields

Hello!

Imagine two tables

CREATE TABLE one ( name varchar(10), content varchar(10) );
CREATE TABLE two ( name varchar(10), something_different varchar(10) );

with some rows in each of them and and a query

SELECT *
INTO new_table
FROM one, two
WHERE one.name=two.name;

If there are matching fields, then the query would return some joined
rows. But it can't, because the column "name" comes twice in the tables!
Of course

SELECT one.*, two.name AS two_name, two.something_different
INTO new_table
FROM one, two
WHERE one.name=two.name;

does the work, because I rename the duplicate columns.

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.

Is there some similar feature in Postgres? I'm asking, because I have
queries over up to seven tables joined and the fields in those tables
are growing larger and larger. But if I add fields, than I have to add
them to each of the SELECT statements also, that they won't be missed
off. That's boaring und faulty.

Any help welcome!

regards,
Thomas
(Vienna, Austria, Europe)

"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

At 18:43 +0300 on 29/08/1999, Dipl.-Ing. Thomas Schallar wrote:


> But if I add fields, than I have to add
> them to each of the SELECT statements also, that they won't be missed
> off. That's boaring und faulty.

One may argue about that. I was told in the past never to use * except when
"just checking", i.e. when running something ad-hoc in psql. In
applications, it is always recommended to write the names of the fields
explicitly, which makes sure you don't include fields which are not really
needed, and you know exactly what you got back and why you got it.

The minute you use an equijoin, you have one field which is truly
redundant, not just by name, but also in value. I think that writing a
query is something you do once, but many many redundant bytes will be
transmitted as a result of being lazy during the writing of the query.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma