Re: How to handle results with column names clash

On 28/09/2010 23:53, Bartlomiej Korupczynski wrote:
> Hi,
>
> 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.
>
> 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,
> 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.

In PHP you can access columns by index, using pg_fetch_array().

However, I think it's better to embrace the pain and use aliases for the
columns with duplicated names - makes your code much easier to read.

You could also create a view which defines the aliases for the columns,
presenting a consistent interface to the PHP code.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie