0

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?

Community
  • 1
  • 1
Galen Long
  • 3,693
  • 1
  • 25
  • 37
  • 2
    Vertica *does* having the USING keyword (try it). The error itself is a different problem -- I have been taught it's best practice to enumerate all column names in any view definition (guard against unexpected schema changes or column renames or alters). If one of your tables has a ton of columns, use the special table `select column_name from columns where table_name = 'foo'` to print them all. You only need to do it once. – wij Apr 13 '16 at 22:49

1 Answers1

2

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;
Adam Silenko
  • 3,025
  • 1
  • 14
  • 30
  • This is a good point - if the other table has fewer columns, I can simply just list those ones and use * for the larger table. wij's comment is best, but since it's not an answer, I'll accept yours. – Galen Long Apr 15 '16 at 04:04
  • In MySQL you can use join_condition `USING (column_list)` for example `a JOIN b USING (key)`, but vertica don't support that syntax – Adam Silenko Apr 15 '16 at 13:16