Thread: How to disable duplicate columns

I have tables with large number of columns some of which are duplicate.
I need to use

SELECT t1.*, t2.* FROM t1 join  t2 using (t)

since I don't know all column names of t1 and t2 tables at design time.

In this case PostgreSQL returns table with duplicate columns.
How to force Postgres to return only first table column  when second table
contains column with same name?

Code to reproduce:

create table t1 ( id integer
  /*, a lot of other columns */ );
create table t2 ( id integer
  /*, a lot of other columns */ );
create table t3 as select t1.*,t2.* from t1 join t2 using (id);

Observed:

Error: Column name is duplicated

Expected:

t3 table should contain single id column

Andrus.

On 4/8/07, Andrus <kobruleht2@hot.ee> wrote:
> I have tables with large number of columns some of which are duplicate.
> I need to use
>
> SELECT t1.*, t2.* FROM t1 join  t2 using (t)
>
> since I don't know all column names of t1 and t2 tables at design time.
>
> In this case PostgreSQL returns table with duplicate columns.
> How to force Postgres to return only first table column  when second table
> contains column with same name?

use can use a natural join:

select * from t1 natural join t2;

this will essentially look for columns that are in both tables, and do
an implicit 'using' on those columns.

be warned, this can lead to surprising behavior -- it only works if
you *want* identically named fields to be part of the join condition,
otherwise the results are undefined.

merlin

On Sun, 8 Apr 2007, Andrus wrote:

> I have tables with large number of columns some of which are duplicate.
> I need to use
>
> SELECT t1.*, t2.* FROM t1 join  t2 using (t)
>
> since I don't know all column names of t1 and t2 tables at design time.
>
> In this case PostgreSQL returns table with duplicate columns.
> How to force Postgres to return only first table column  when second table
> contains column with same name?

There are a few cases where duplicate columns are trimmed, such as select
* from something with a join ...  using or natural join only should result
in one output column for the joined upon column names.

> Code to reproduce:
>
> create table t1 ( id integer
>   /*, a lot of other columns */ );
> create table t2 ( id integer
>   /*, a lot of other columns */ );
> create table t3 as select t1.*,t2.* from t1 join t2 using (id);

If only id were duplicated, then select * from t1 join t2 using(id)
should work.  If other columns are duplicated, then that won't work, but
generally just choosing the first column with a name seems bizarre in that
case.