CODEWITHSUNDEEP
sqljoinduplicatesamazon-redshift
Mobix
Mobix

Reputation: 103

Amazon Redshift join variable duplicated with select *

I'm trying to create a new table in Redshift

The code used to create a new table works on it's own but when I wrap it in create table it stops working

Invalid operation: column name "Number" is duplicated;

create table test
as
(select 
a.*
,b.*
from a
inner join b
on a.number = b.number
);

Any ideas what's happening? I don't want to specify every single column of b just so i can omit b.numbe

Upvotes: 1

Views: 1664

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269813

In a view, you should really select the columns explicitly that you want. However, if the only duplicated column is number you can get around this using using:

select *
from a inner join
     b
     using (number);

Using is smart enough to include only one version of the join column when you use select *.

Upvotes: 3

Related Questions