Adding Table Columns
You add a column to a persistent table with ALTER TABLE…ADD COLUMN:
ALTER TABLE … ADD COLUMN [IF NOT EXISTS] column datatype [column‑constraint] [ENCODING encoding‑type] [PROJECTIONS (projections‑list) | ALL PROJECTIONS ]
Before you add columns to a table, verify that all its superprojections are up to date.
Table Locking
When you use ADD COLUMN to alter a table, Vertica takes an O lock on the table until the operation completes. The lock prevents DELETE, UPDATE, INSERT, and COPY statements from accessing the table. The lock also blocks SELECT statements issued at SERIALIZABLE isolation level, until the operation completes.
Adding a column to a table does not affect K-safety of the physical schema design.
You can add columns when nodes are down.
Adding New Columns to Projections
When you add a column to a table, Vertica automatically adds the column to superprojections of that table. The ADD…COLUMN clause can also specify to add the column to one or more non-superprojections, with one of these options:
PROJECTIONS(projections-list): Adds the new column to one or more projections of this table, specified as a comma-delimted list of projection base names. Vertica adds the column to all buddies of each projection. The projection list cannot include projections with pre-aggregated data such as live aggregate projections; otherwise, Vertica rolls back theALTER TABLEstatement.ALL PROJECTIONSadds the column to all projections of this table, excluding projections with pre-aggregated data.
For example, the store_orders table has two projections—superprojection store_orders_super, and user-created projection store_orders_p. The following ALTER TABLE…ADD COLUMN statement adds column expected_ship_date to the store_orders table. Because the statement omits the PROJECTIONS option, Vertica adds the column only to the table's superprojection:
=> ALTER TABLE public.store_orders ADD COLUMN expected_ship_date date; ALTER TABLE => SELECT projection_column_name, projection_name FROM projection_columns WHERE table_name ILIKE 'store_orders' ORDER BY projection_name , projection_column_name; projection_column_name | projection_name ------------------------+-------------------- order_date | store_orders_p_b0 order_no | store_orders_p_b0 ship_date | store_orders_p_b0 order_date | store_orders_p_b1 order_no | store_orders_p_b1 ship_date | store_orders_p_b1 expected_ship_date | store_orders_super order_date | store_orders_super order_no | store_orders_super ship_date | store_orders_super shipper | store_orders_super (11 rows)
The following ALTER TABLE…ADD COLUMN statement includes the PROJECTIONS option. This specifies to include projection store_orders_p in the add operation. Vertica adds the new column to this projection and the table's superprojection:
=> ALTER TABLE public.store_orders ADD COLUMN delivery_date date PROJECTIONS (store_orders_p); => SELECT projection_column_name, projection_name FROM projection_columns WHERE table_name ILIKE 'store_orders' ORDER BY projection_name, projection_column_name; projection_column_name | projection_name ------------------------+-------------------- delivery_date | store_orders_p_b0 order_date | store_orders_p_b0 order_no | store_orders_p_b0 ship_date | store_orders_p_b0 delivery_date | store_orders_p_b1 order_date | store_orders_p_b1 order_no | store_orders_p_b1 ship_date | store_orders_p_b1 delivery_date | store_orders_super expected_ship_date | store_orders_super order_date | store_orders_super order_no | store_orders_super ship_date | store_orders_super shipper | store_orders_super (14 rows)
Updating Associated Table Views
Adding new columns to a table that has an associated view does not update the view's result set, even if the view uses a wildcard (*) to represent all table columns. To incorporate new columns, you must recreate the view.