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

Answer

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 *.