Re: How to handle results with column names clash

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