Search code examples
sqlverticavsql

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?

Solution

  • 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;