add column

Naveen_Kumar

Is there any way to add a column to a table if this column is not there in the table?
like :
create table t(x integer,y varchar);
if not exists (select * from t where column_name =y) then alter table t add column  y varchar .
//
alter table t add column  y varchar; can add if y is not there, else it will give error rollback :......

Rahul_Choudhary

Hi Naveen

You can certainly go ahead and add a new column to the existing table.As per Vertica Documentation when you add a new column to a table using ALTER TABLE ADD COLUMN, the default expression for the new column can either consist of an expression that evaluates to a constant or is a derived expression involving other columns of the same table.

The ADD COLUMN syntax performs the following operations:

• Inserts the default value for existing rows. For example, if the default expression is CURRENT_TIMESTAMP, all rows have the current timestamp.
• Automatically adds the new column with a unique projection column name to all superprojections of the table.
• Populates the column according to the column-constraint (DEFAULT, for example).
• Takes an O lock on the table until the operation completes, in order to prevent DELETE, UPDATE, INSERT, and COPY statements from affecting the table. SELECT statements issued at SERIALIZABLE isolation level are also blocked until the operation completes

Note: Adding a column to a table does not affect the K-safety of the physical schema design, and you can add columns when nodes are down.

Kindly go through the below documented link for more information on this.

https://my.vertica.com/docs/6.1.x/HTML/index.htm#18129.htm

Thanks

Rahul