ALTER TABLE

Specifies a schema. If multiple schemas are defined in the database, include the schema name. For example:

myschema.thisDbObject

The table to alter.

Adds a column to the table and to its superprojections:

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

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

• column‑constraint specifies a column constraint as follows:

  [CONSTRAINT constraint-name]
    { {NULL | NOT NULL} | DEFAULT expression }
  

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

• RESTRICT (default) adds the new column to pre-join projections only if they are anchored to the updated table. CASCADE updates all pre-join projections where the table is specified, regardless of whether they are anchored to it.

• PROJECTIONS specifies adding the new column to one or more existing projections.

Adds a constraint to a table that does not have any associated projections.

See About Constraints in the Administrator's Guide.

Alters a setting for column-name:

ALTER COLUMN column‑name column‑setting

column‑setting can be one of the following:

• SET DEFAULT expression sets column values to the specified expression.

  Note: Altering an existing table column to specify a DEFAULT expression has no effect on existing values in that column. Vertica applies the DEFAULT expression only on new rows when they are added to the table, through load operations such as INSERT and COPY. To refresh the entire column with its DEFAULT expression, update the column as follows:

  UPDATE table-name SET column-name=DEFAULT;                

• DROP DEFAULT drops the DEFAULT setting.
• SET USING expression specifies to set this column's values from the result set returned by expression. When you make this change, Vertica automatically calls the function REFRESH_COLUMNS, which populates the column from the result set returned by expression. Thereafter, the column contents are refreshed only on explicit calls to REFRESH_COLUMNS.
• DROP SET USING drops the SET USING setting.
• SET DEFAULT USING expression combines SET DEFAULT and SET USING clauses, setting both to the same expression. The DDL for the column specifies both clauses.
• DROP DEFAULT USING drops the column's DEFAULT and SET USING settings.
• SET NOT NULL disallows inserting null values in the column.
• DROP NOT NULL allows null values in the column.
• SET DATA TYPE datatype resets the column data type, if Vertica supports the change. No restrictions apply to changing data types of external tables.

Applies to automatic enforcement of primary key, unique key, and check constraints:

ALTER CONSTRAINT constraint‑name {ENABLED | DISABLED}

See Altering Constraint Enforcement in the Administrator's Guide.

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.

Drops the specified column from the table and that column's ROS containers:

DROP [COLUMN] column‑name [CASCADE | RESTRICT]

You can qualify DROP COLUMN with one of these options:

• CASCADE is required if the table column to drop has dependencies.
• RESTRICT drops the column only from the given table.

See Dropping Table Columns in the Administrator's Guide.

Changes the table owner. See Changing Table Ownership in the Administrator's Guide.

Specifies how to repartition table data storage, where partition‑expression resolves to a value derived from one or more table columns:

PARTITION BY partition‑expression [REORGANIZE]

This option is invalid for external tables.

If a partitioning clause includes REORGANIZE, Vertica immediately implements the partition clause by initiating a mergeout operation. This operation drops existing partition keys and repartitions table data.

Specifies default inheritance of schema privileges for this table:

• EXCLUDE [SCHEMA] PRIVILEGES (default) disables inheritance of privileges from the schema.
• INCLUDE [SCHEMA] PRIVILEGES grants the table the same privileges granted to its schema. 
• MATERIALIZE: 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

Note: If inherited privileges are disabled at the database level, schema privileges can still be materialized.

For more information see Grant Inherited Privileges.

Immediately removes partitioning on a table. The ROS containers are not immediately altered, but they are later cleaned by the Tuple Mover.

Renames the specified column within the table. See Renaming Columns in the Administrator's Guide.

Changes the column's data type to any type whose conversion does not require storage reorganization.

See Changing a Column Data Type in the Administrator's Guide.

Moves the table from one schema to another. Vertica automatically moves all projections that are anchored to the source table to the destination schema. For details, see Moving Tables to Another Schema in the Administrator's Guide.

Specifies default load behavior for all DML operations on this table, such as INSERT and COPY, one of the following:

• AUTO (default): Initially loads data into WOS, suitable for smaller bulk loads.
• DIRECT: Loads data directly into ROS containers, suitable for large (>100 MB) bulk loads.
• TRICKLE: Loads data only into WOS, suitable for frequent incremental loads.

For details, see Choosing a Load Method in the Administrator's Guide.

Renames one or more tables:

RENAME TO new‑table‑name[,...]

The following requirements apply:

• The new table name must be unique among all tables, views and projections within the same schema.
• If you specify multiple tables to rename, the source and target lists must have the same number of names.
• Renaming a table requires USAGE and CREATE privileges on the schema that contains the view.