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 [EDIT: wij pointed out that Vertica SQL does have USING
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
?
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;