Adding Table Columns

You add a column to a persistent table with the ALTER TABLE clause ADD COLUMN:

ALTER TABLE
... 
ADD COLUMN column‑name datatype 
  [column‑constraint]
  [ENCODING encoding‑type]
  [RESTRICT | CASCADE]
  [PROJECTIONS (projection-name [,...]) ]

Note: Before you can add columns to a table, all superprojections that are anchored to it must be up to date.

Default Add Operations

When you update a table with a new columns, Vertica always executes the following actions:

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.

If you use CASCADE, Vertica also takes O locks on all anchor tables of any pre-join projections associated with the target table. Consequently, SELECT statements issued on those tables at SERIALIZABLE isolation level are blocked 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.

Using the PROJECTIONS Option to Add New Columns to Projections

When you add a new column to a table, you can use the PROJECTIONS option to simultaneously add the new column to one or more existing projections.

Note: The PROJECTIONS option accepts the base name of a projection. When you specify the base name, Vertica updates the projection along with any buddy projections.

The following example creates a table and projection, and adds a new column to the table and to the projection.

  1. Create a sample table and add content.

    => CREATE TABLE t1 (col1 int, col2 int, col3 int);
    => INSERT INTO t1 VALUES (1, 2, 3);
    => INSERT INTO t1 VALUES (4, 5, 6);
  2. Create a projection.

    => CREATE PROJECTION myproj (col1) AS SELECT col1 FROM t1;
  3. Add a new column to the table, simultaneously adding the column to the existing projection.

    => ALTER TABLE t1 ADD COLUMN newcol INT PROJECTIONS(myproj); 
  4. Check that the new column is included in the projection.

    => SELECT table_name, table_column_name, projection_name FROM projection_columns WHERE projection_name LIKE 'myproj%';
    table_name | table_column_name | projection_name
    -----------+-------------------+-----------------
     t1        | col1              | myproj_b0
     t1        | col1              | myproj_b1
     t1        | newcol            | myproj_b0
     t1        | newcol            | myproj_b1
    (4 rows)

The following example adds a new column and defines the column with a column-constraint DEFAULT expression. (For details about expressions, see Defining Column Values.) When you include a DEFAULT expression, the columns that you reference in the expression must have already been included in the existing projection. If the columns from the table you are altering are referenced in the expression, but were not included in the existing projection, Vertica rolls back. The following example illustrates.

  1. Create two tables and add content.

    => CREATE TABLE dimension (pk INT, val INT, colx INT);
    => CREATE TABLE fact (pk INT, fk INT, cols INT);
    => INSERT INTO dimension VALUES (1, 2, 3);
    => INSERT INTO fact VALUES (4, 5, 6);
  2. Create a projection that includes the pk column from the table fact.

    => CREATE PROJECTION fact_pk AS SELECT pk FROM fact;
  3. Attempt to add a new column to the table fact. Note that the sample references the column fact.fk, which is not included in the existing projection. Vertica rolls back.

    => ALTER TABLE fact ADD COLUMN val INT 
       DEFAULT (SELECT val FROM dimension WHERE dimension.pk = fact.fk) PROJECTIONS (fact_pk);
    ROLLBACK 7871:  Projection "public.fact_pk_b0" refers to column "fk" referenced in the added column's default expression

What follows is the same example, but in this case the projection includes both columns.

  1. Create a sample table and add content.

    => CREATE TABLE t1 (col1 int, col2 int, col3 int);
    => INSERT INTO t1 VALUES (1, 2, 3);
    => INSERT INTO t1 VALUES (4, 5, 6);
  2. Create a projection that includes both the pk and fk columns from the table fact.

    => CREATE PROJECTION fact_pk AS SELECT pk, fk FROM fact;
  3. Add the new column using a column-constraint DEFAULT expression.

    => ALTER TABLE fact ADD COLUMN val INT 
       DEFAULT (SELECT val FROM dimension WHERE dimension.pk = fact.fk) PROJECTIONS (fact_pk);
  4. Check that the new column val is included in the projection.

    => SELECT table_name, table_column_name, projection_name FROM projection_columns WHERE projection_name LIKE 'fact_pk%';
     table_name | table_column_name | projection_name
    ------------+-------------------+-----------------
     fact       | pk                | fact_pk_b0
     fact       | pk                | fact_pk_b1
     fact       | fk                | fact_pk_b0
     fact       | fk                | fact_pk_b1
     fact       | val               | fact_pk_b0
     fact       | val               | fact_pk_b1

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.