From: | Darren Duncan <darren(at)darrenduncan(dot)net> |
---|---|
To: | Bartlomiej Korupczynski <bartek-sql(at)klolik(dot)org>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: How to handle results with column names clash |
Date: | 2010-09-29 02:01:37 |
Message-ID: | 4CA29E01.1030901@darrenduncan.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andy Colson | 2010-09-29 02:15:28 | Re: [RRR] [HACKERS] Commitfest: The Good, The Bad, and the Ugly |
Previous Message | Tom Lane | 2010-09-29 01:58:10 | Re: Restore problem from 8.4 backup to 9.0 |