This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

ALTER TABLE

Modifies the metadata of an existing table.

Modifies the metadata of an existing table. All changes are auto-committed.

Syntax

ALTER TABLE [[database.]schema.]table {
    ADD COLUMN [ IF NOT EXISTS ] column datatype
       [ column-constraint ]
       [ ENCODING encoding-type ]
       [ PROJECTIONS (projections-list) | ALL PROJECTIONS ]
    | ADD table-constraint
    | ALTER COLUMN column {
        ENCODING encoding-type PROJECTIONS (projection-list)
        | { SET | DROP } expression }
    | ALTER CONSTRAINT constraint-name { ENABLED | DISABLED }
    | DISK_QUOTA { value | SET NULL }
    | DROP CONSTRAINT constraint-name [ CASCADE | RESTRICT ]
    | DROP [ COLUMN ] [ IF EXISTS ] column [ CASCADE | RESTRICT ]
    | FORCE OUTER integer
    | { INCLUDE | EXCLUDE | MATERIALIZE } [ SCHEMA ] PRIVILEGES
    | OWNER TO owner
    | partition-clause [ REORGANIZE ]
    | REMOVE PARTITIONING
    | RENAME [ COLUMN ] name TO new-name
    | RENAME TO new-table-name[,...]
    | REORGANIZE
    | SET {
        ActivePartitionCount { count | DEFAULT }
        | IMMUTABLE ROWS
        | MERGEOUT { 1 | 0 }
        | SCHEMA schema }
}

Parameters

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

table
The table to alter.
ADD COLUMN
Adds a column to the table and, by default, to all its superprojections: 
ADD COLUMN [IF NOT EXISTS]
    column datatype 
    [ NULL | NOT NULL ]
    [ ENCODING encoding-type]
    [ PROJECTIONS (projections-list) | ALL PROJECTIONS]

Restrictions on columns of complex types also apply to columns that you add using ADD COLUMN.

The optional IF NOT EXISTS clause generates an informational message if column already exists under the specified name. If you omit this option and column exists, Vertica generates a ROLLBACK error message.

You can qualify the new column definition with one of these options:

  • column-constraint specifies a column constraint as follows:

    
  {NULL | NOT NULL}
  | [ DEFAULT default-expr ] [ SET USING using-expr ] } | DEFAULT USING exp}

  • ENCODING specifies the column's encoding type, by default set to AUTO.

  • PROJECTIONS adds the new column to one or more existing 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 the ALTER TABLE statement.

  • ALL PROJECTIONS adds the column to all projections of this table, excluding projections with pre-aggregated data.

ADD table-constraint
Adds a constraint to a table that does not have any associated projections.
ALTER COLUMN
You can alter an existing column in one of two ways:

  • Set encoding on a column for one or more projections of this table:

    ENCODING encoding-type PROJECTIONS (projections-list)

    where projections-list is a comma-delimited list of projections to update with the new encoding. You can specify each projection in two ways:

    • Projection base name: Update all projections that share this base name.

    • Projection name: Update the specified projection. If the projection is segmented, the change is propagated to all buddies.

    If one of the projections does not contain the target column, Vertica returns with a rollback error.

    For details, see Projection Column Encoding.

  • Set or drop a setting for a column of scalar data, including primitive arrays:

    SET { DEFAULT expression
          | USING expression
    | DEFAULT USING expression
    | NOT NULL
    | DATA TYPE datatype
}
DROP { DEFAULT
     | SET USING
     | DEFAULT USING
     | NOT NULL
}

    You cannot change the data type of a column of any complex type that is neither a scalar type nor an array of scalar types. One exception applies: in external tables, you can change a primitive column type to a complex type.

    Setting a DEFAULT or SET USING expression has no effect on existing column values. To refresh the column with its DEFAULT or SET USING expression, update it as follows

    • SET USING column: Call REFRESH_COLUMNS on the table.

    • DEFAULT column: update the column as follows:

      UPDATE table-name SET column-name=DEFAULT;

Altering a column with DEFAULT or SET USING can increase disk usage, which can cause the operation to fail if it would violate the table or schema disk quota.

ALTER CONSTRAINT
Specifies whether to enforce primary key, unique key, and check constraints: 
ALTER CONSTRAINT constraint-name {ENABLED | DISABLED}
DISK_QUOTA
One of the following:

  • A string, an integer followed by a supported unit: K, M, G, or T. If the new value is smaller than the current usage, the operation succeeds but no further disk space can be used until usage is reduced below the new quota.

  • SET NULL to remove a quota.

For more information, see Disk quotas.

DROP CONSTRAINT
Drops the specified table constraint from the table: 
DROP CONSTRAINT constraint-name [CASCADE | RESTRICT]

You can qualify DROP CONSTRAINT with one of these options:

  • CASCADE: Drops a constraint and all dependencies in other tables.

  • RESTRICT: Does not drop a constraint if there are dependent objects. Same as the default behavior.

Dropping a table constraint has no effect on views that reference the table.

DROP [COLUMN]
Drops the specified column from the table and that column's ROS containers: 
DROP [COLUMN] [IF EXISTS] column [CASCADE | RESTRICT]

You can qualify DROP COLUMN with one of these options:

  • IF EXISTS generates an informational message if the column does not exist. If you omit this option and the column does not exist, Vertica generates a ROLLBACK error message.

  • CASCADE is required if the column has dependencies.

  • RESTRICT drops the column only from the given table.

The column's table cannot be immutable.

See Dropping table columns.

FORCE OUTER integer
Specifies whether a table is joined to another as an inner or outer input. For details, see Controlling join inputs.
{INCLUDE | EXCLUDE | MATERIALIZE} [SCHEMA] PRIVILEGES
Specifies default inheritance of schema privileges for this table:

  • EXCLUDE PRIVILEGES (default) disables inheritance of privileges from the schema.

  • INCLUDE PRIVILEGES grants the table the same privileges granted to its schema.

  • MATERIALIZE PRIVILEGES copies grants to the table and creates a GRANT object on the table. This disables the inherited privileges flag on the table, so you can:

    • Grant more specific privileges at the table level.

    • Use schema-level privileges as a template.

    • Move the table to a different schema.

    • Change schema privileges without affecting the table.

See also Setting privilege inheritance on tables and views.

OWNER TO owner
Changes the table owner.
partition-clause [REORGANIZE]
Invalid for external tables, logically divides table data storage through a PARTITION BY clause: 
PARTITION BY partition-expression
  [ GROUP BY group-expression ]
  [ SET ACTIVEPARTITIONCOUNT integer ]

For details, see Partition clause.

If you qualify the partition clause with REORGANIZE and the table previously specified no partitioning, the Vertica Tuple Mover immediately implements the partition clause. If the table previously specified partitioning, the Tuple Mover evaluates ROS storage containers and reorganizes them as needed to conform with the new partition clause.

REMOVE PARTITIONING
Specifies to remove partitioning from a table definition. The Tuple Mover subsequently removes existing partitions from ROS containers.
RENAME [COLUMN]
Renames the specified column within the table. The column's table cannot be immutable.
RENAME TO
Renames one or more tables: 
RENAME TO new-table-name[,...]

The following requirements apply:

  • The renamed table must be in the same schema as the original table.

  • The new table name conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.

  • If you specify multiple tables to rename, the source and target lists must have the same number of names.

REORGANIZE
Valid only for partitioned tables, invokes the Tuple Mover to reorganize ROS storage containers as needed to conform with the table's current partition clause. ALTER TABLE...REORGANIZE and Vertica meta-function PARTITION_TABLE operate identically.

REORGANIZE can also qualify a new partition clause.

SET
Changes a table setting, one of the following:

  • ActivePartitionCount { count | DEFAULT }, valid only for partitioned tables, specifies how many partitions are active for this table, one of the following:

    • count: Unsigned integer, supersedes configuration parameter ActivePartitionCount.

    • DEFAULT: Removes the table-level active partition count. The table obtains its active partition count from the configuration parameter ActivePartitionCount.

    For details on usage, see Active and inactive partitions.

  • IMMUTABLE ROWS prevents changes to table row values by blocking DML operations such as UPDATE and DELETE. Once set, table immutability cannot be reverted.

    You cannot set a flattened table to be immutable. For details on all immutable table restrictions, see Immutable tables.

  • MERGEOUT { 1 | 0 } specifies whether to enable or disable mergeout to ROS containers that consolidate projection data of this table. By default, mergeout is enabled (1) on all tables.

  • SCHEMA schema-name moves the table from its current schema to schema-name. Vertica automatically moves all projections that are anchored to the source table to the destination schema. It also moves all IDENTITY columns to the destination schema. For details, see Moving tables to another schema

Privileges

Non-superuser: USAGE on the schema and one of the following:

  • Table owner

  • ALTER privileges

Non-superusers must also have SELECT privileges to enable or disable constraint enforcement, or remove partitioning.

For certain operations, non-superusers must have the following schema privileges:

  • To rename a table: CREATE, USAGE

  • To move a table to another schema: USAGE on the source schema, CREATE on the destination schema

Restrictions for complex types

Complex types used in native tables have some restrictions, in addition to the restrictions for individual types listed on their reference pages:

  • A native table must have at least one column that is a primitive type or a native array (one-dimensional array of a primitive type). If a flex table has real columns, it must also have at least one column satisfying this restriction.

  • Complex type columns cannot be used in ORDER BY or PARTITION BY clauses nor as FILLER columns.

  • Complex type columns cannot have constraints.

  • Expressions returning complex types cannot be used as projection columns, and projections cannot be segmented or ordered by columns of complex types.

  • Tables with columns of complex types cannot use DEFAULT and SET USING.

Exclusive ALTER TABLE clauses

The following ALTER TABLE clauses cannot be combined with another ALTER TABLE clause:

  • ADD COLUMN

  • DROP COLUMN

  • RENAME COLUMN

  • SET SCHEMA

  • RENAME [TO]

Node down limitations

Enterprise Mode only

The following ALTER TABLE operations are not supported when one or more database cluster nodes are down:

  • ALTER COLUMN ... ADD table-constraint

  • ALTER COLUMN ... SET DATA TYPE

  • ALTER COLUMN ... { SET DEFAULT | DROP DEFAULT }

  • ALTER COLUMN ... { SET USING | DROP SET USING }

  • ALTER CONSTRAINT

  • DROP COLUMN

  • DROP CONSTRAINT

Pre-aggregated projection restrictions

You cannot modify the metadata of anchor table columns that are included in live aggregate or Top-K projections. You also cannot drop these columns. To make these changes, you must first drop all live aggregate and Top-K projections that are associated with it.

External table restrictions

Not all ALTER TABLE options pertain to external tables. For instance, you cannot add a column to an external table, but you can rename the table:

=> ALTER TABLE mytable RENAME TO mytable2;
ALTER TABLE

Locked tables

If the operation cannot obtain an O lock on the target table, Vertica tries to close any internal Tuple Mover sessions that are running on that table. If successful, the operation can proceed. Explicit Tuple Mover operations that are running in user sessions do not close. If an explicit Tuple Mover operation is running on the table, the operation proceeds only when the operation is complete.

See also

1 - Projection column encoding

After you create a table and its projections, you can call ALTER TABLE...ALTER COLUMN to set or change the encoding type of an existing column in one or more projections.

After you create a table and its projections, you can call ALTER TABLE...ALTER COLUMN to set or change the encoding type of an existing column in one or more projections. For example:

ALTER TABLE store.store_dimension ALTER COLUMN store_region
  ENCODING rle PROJECTIONS (store.store_dimension_p1_b0, store.store_dimension_p2);

In this example, the ALTER TABLE statement specifies to set RLE encoding on column store_region for two projections: store_dimension_p1_b0 and store_dimension_p2. The PROJECTIONS list references the two projections by their projection name and base name, respectively. You can reference a projection either way; in both cases, the change is propagated to all buddies of the projection and stored in its DDL accordingly:

=> select export_objects('','store.store_dimension');

                          export_objects
------------------------------------------------------------------
CREATE TABLE store.store_dimension
(
    store_key int NOT NULL,
    store_name varchar(64),
    store_number int,
    store_address varchar(256),
    store_city varchar(64),
    store_state char(2),
    store_region varchar(64)
);

CREATE PROJECTION store.store_dimension_p1
(
 store_key,
 store_name,
 store_number,
 store_address,
 store_city,
 store_state,
 store_region ENCODING RLE
)
AS
 SELECT store_dimension.store_key,
        store_dimension.store_name,
        store_dimension.store_number,
        store_dimension.store_address,
        store_dimension.store_city,
        store_dimension.store_state,
        store_dimension.store_region
 FROM store.store_dimension
 ORDER BY store_dimension.store_key
SEGMENTED BY hash(store_dimension.store_key) ALL NODES KSAFE 1;

CREATE PROJECTION store.store_dimension_p2
(
 store_key,
 store_name,
 store_number,
 store_address,
 store_city,
 store_state,
 store_region ENCODING RLE
)
AS
 SELECT ...

2 - Table-constraint

Table-constraint

Adds a constraint to table metadata. You can specify table constraints with CREATE TABLE, or add a constraint to an existing table with ALTER TABLE. For details, see Setting constraints.

Syntax

[ CONSTRAINT constraint-name ]
{
... PRIMARY KEY (column[,... ]) [ ENABLED | DISABLED ]
... | FOREIGN KEY (column[,... ] ) REFERENCES table [ (column[,...]) ]
... | UNIQUE (column[,...]) [ ENABLED | DISABLED ]
... | CHECK (expression) [ ENABLED | DISABLED ]
}

Parameters

CONSTRAINT constraint-name
Assigns a name to the constraint. Vertica recommends that you name all constraints.
PRIMARY KEY
Defines one or more NOT NULL columns as the primary key as follows: 
PRIMARY KEY (column[,...]) [ ENABLED | DISABLED]

You can qualify this constraint with the keyword ENABLED or DISABLED. See Enforcing Constraints below.

If you do not name a primary key constraint, Vertica assigns the name C_PRIMARY.

FOREIGN KEY
Adds a referential integrity constraint defining one or more columns as foreign keys as follows: 
FOREIGN KEY (column[,... ]) REFERENCES table [(column[,... ])]

If you omit column, Vertica references the primary key in table.

If you do not name a foreign key constraint, Vertica assigns the name C_FOREIGN.

UNIQUE
Specifies that the data in a column or group of columns is unique with respect to all table rows, as follows: 
UNIQUE (column[,...]) [ENABLED | DISABLED]

You can qualify this constraint with the keyword ENABLED or DISABLED. See Enforcing Constraints below.

If you do not name a unique constraint, Vertica assigns the name C_UNIQUE.

CHECK
Specifies a check condition as an expression that returns a Boolean value, as follows: 
CHECK (expression) [ENABLED | DISABLED]

You can qualify this constraint with the keyword ENABLED or DISABLED. See Enforcing Constraints below.

If you do not name a check constraint, Vertica assigns the name C_CHECK.

Privileges

Non-superusers: table owner, or the following privileges:

Enforcing constraints

A table can specify whether Vertica automatically enforces a primary key, unique key or check constraint with the keyword ENABLED or DISABLED. If you omit ENABLED or DISABLED, Vertica determines whether to enable the constraint automatically by checking the appropriate configuration parameter:

  • EnableNewPrimaryKeysByDefault

  • EnableNewUniqueKeysByDefault

  • EnableNewCheckConstraintsByDefault

For details, see Constraint enforcement.

Examples

The following example creates a table (t01) with a primary key constraint.

CREATE TABLE t01 (id int CONSTRAINT sampleconstraint PRIMARY KEY);
CREATE TABLE

This example creates the same table without the constraint, and then adds the constraint with ALTER TABLE ADD CONSTRAINT

CREATE TABLE t01 (id int);
CREATE TABLE

ALTER TABLE t01 ADD CONSTRAINT sampleconstraint PRIMARY KEY(id);
WARNING 2623:  Column "id" definition changed to NOT NULL
ALTER TABLE

The following example creates a table (addapk) with two columns, adds a third column to the table, and then adds a primary key constraint on the third column.

=> CREATE TABLE addapk (col1 INT, col2 INT);
CREATE TABLE

=> ALTER TABLE addapk ADD COLUMN col3 INT;
ALTER TABLE

=> ALTER TABLE addapk ADD CONSTRAINT col3constraint PRIMARY KEY (col3) ENABLED;
WARNING 2623:  Column "col3" definition changed to NOT NULL
ALTER TABLE

Using the sample table addapk, check that the primary key constraint is enabled (is_enabled is t).

=> SELECT constraint_name, column_name, constraint_type, is_enabled FROM PRIMARY_KEYS WHERE table_name IN ('addapk');

 constraint_name | column_name | constraint_type | is_enabled
-----------------+-------------+-----------------+------------
 col3constraint  | col3        | p               | t
(1 row)

This example disables the constraint using ALTER TABLE ALTER CONSTRAINT.

=> ALTER TABLE addapk ALTER CONSTRAINT col3constraint DISABLED;

Check that the primary key is now disabled (is_enabled is f).

=> SELECT constraint_name, column_name, constraint_type, is_enabled FROM PRIMARY_KEYS WHERE table_name IN ('addapk');

 constraint_name | column_name | constraint_type | is_enabled
-----------------+-------------+-----------------+------------
 col3constraint  | col3        | p               | f
(1 row)

For a general discussion of constraints, see Constraints. For additional examples of creating and naming constraints, see Naming constraints.