Bartlomiej Korupczynski wrote:
I'm curious how do you handle results from multiple tables with
repeated column names. For example:
# CREATE TABLE c1 (id integer PRIMARY KEY, address inet);
# CREATE TABLE c2 (id integer PRIMARY KEY, address text);
# SELECT * FROM c1 JOIN c2 USING (id);
id | address | address
----+---------+---------
(0 rows)
or:
# SELECT * FROM c1, c2 WHERE c1.id=c2.id;
id | address | id | address
----+---------+----+---------
(0 rows)
Now lets say we want access results from PHP/perl/etc using column
names. We have "address" from c1, and the same from c2. We can't even
distinguish which one is from which table.
The only proper solution is for every resultset column to have a distinct
unqualified name, full-stop.
If you are joining tables that use the same name for different things, then you
have two good options:
1. Rename the table columns to be unique, such as using "inet_addr" and
"street_addr".
2. Use "AS" in your query to give the result columns unique names.
Similarly, id columns should be more descriptive to say what they are the id of
(eg, artist_id, track_id, etc), and use the same name for columns containing the
same data, and different names for different data, so approach #1; the main time
to deviate from this is if you have several columns with the same kind of data,
and then you use #2.
I see two available possibilities:
1. rename one or each column (eg. prefix with table name), but it's not
always acceptable and makes JOIN ... USING syntax useless (and it's
messy to change to JOIN .. ON for many columns), it would also not work
if we join on the same table twice or more,
Don't prefix with the table name if that doesn't make sense. In your case, you
could call the field "c_id" in both tables for example.
Generally speaking, you *do* want a situation that lets you use "JOIN ... USING"
wherever possible.
2. select each column explicitly:
SELECT c1.id, c1.address AS c1_address, c2.address AS c2.address
but this is nightmare for tables with many columns, especially if the
schema changes frequently.
If you give the table columns good names, you generally won't have to do that.
Someone could say, that if we JOIN on some column, then it's the same
value, but it does not need to be always true -- we can join on
different columns in different queries.
Yes you can, but with a well designed schema you would be joining on same-named
columns most of the time, and for the rest, you can use AS.
Any other ideas?
I've given mine.
3. Suggestion, but it would be probably hard to implement: to make SQL
engine prefix each returned column with table alias. Of course it would
not be a default behavior, but it would be enabled by some session wide
setting.
# SELECT * FROM c1, c2 WHERE c1.id=c2.id;
c1.id | c1.address | c2.id | c2.address
[...]
# SELECT * FROM c1 JOIN c2 USING (id);
??id | c1.address | c2.address
As JOIN returns only one copy of id, it would be hard to decide about
results (could return one copy for each alias like above).
4. Probably also hard to implement, something like:
# SELECT c1.* AS c1_*, c2.* AS c2_* FROM ...
Some DBMSs already do this, and is a *bad* idea.
The fact that SQL lets you have a rowset with column names either duplicated or
missing is a horrible misfeature and one shouldn't rely on it.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
