Generate a Notice Not an Error When Adding a Duplicate Column to a Table: Quick Tip

When adding a column to a table in Vertica 9.2, the optional clause IF NOT EXISTS generates an informational message if the column already exists under the specified name. If you omit this option and the column exists, Vertica generates a ROLLBACK error message. Example: dbadmin=> CREATE TABLE test (c1 INT, c2 INT); CREATE TABLE dbadmin=> ALTER TABLE test ADD COLUMN c2 INT; ROLLBACK 3145: Duplicate column name dbadmin=> ALTER TABLE test ADD COLUMN IF NOT EXISTS c2 INT; NOTICE 8778: Duplicate column name; nothing was done ALTER TABLE dbadmin=> SELECT error_level, message dbadmin-> FROM error_messages dbadmin-> WHERE session_id = CURRENT_SESSION() dbadmin-> ORDER BY event_timestamp DESC; error_level | message ————-+—————————————– NOTICE | Duplicate column name; nothing was done ERROR | Duplicate column name (2 rows) Helpful Link: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/ALTERTABLE.htm Have fun!