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

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert admin

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/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/ALTERTABLE.htm

Have fun!

Sign In or Register to comment.