nightingalen nightingalen - 7 months ago 14
SQL Question

Vertica create view from join duplicate key columns

When I try to create a view in Vertica from a join,

CREATE VIEW c AS
SELECT * FROM a JOIN b ON a.key = b.key;


I get an error because the key column is duplicated:

ROLLBACK 5450: View definition can not contain duplicate column names "key"


In non-Vertica SQL, I know I can use the
USING
keyword when both of the key column names are the same, but Vertica doesn't have
USING
[EDIT: wij pointed out that Vertica SQL does have
USING
]. I could also list out columns explicitly instead of selecting
*
, but one of the tables has hundreds of columns and I want them all (except for the duplicate key). There also doesn't seem to be an easy way to select all but one column.

Is there a way to select only one key column in a join when the column names are the same without
USING
?

Answer

write list of column (because both tables have field key), for example:

CREATE VIEW c AS 
SELECT a.*, b.field1, b.field2 FROM a JOIN b ON a.key = b.key;