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



Specifies a schema, by default public. If schema is any schema other than public, you must supply the schema name. For example:


If you specify a database, it must be the current database.


The table to alter.


Adds a column to the table and to its superprojections:

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

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 to add the new column to one or more projections, where projection is the projection base name and is not a superprojection. Vertica adds the column to all buddies of each projection.
ADD table‑constraint

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

See Constraints in the Administrator's Guide.


Alters a setting for column:

ALTER COLUMN column 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. See Changing a Column Data Type in the Administrator's Guide. No restrictions apply to changing data types of external tables.

Specifies whether to enforce primary key, unique key, and check constraints:


See Constraint Enforcement in the Administrator's Guide.


Drops the specified table constraint from the table:


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:


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.

FORCE OUTER integer Specifies whether a table is joined to another as an inner or outer input. For details, see Controlling Join Inputs in Analyzing 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.

See also Setting Privileges Inheritance on Tables and Views in the Administrator's Guide.

OWNER TO owner

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

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 ] [ 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.


Specifies to remove partitioning from a table definition. The Tuple Mover subsequently removes existing partitions from ROS containers.


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


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.


Valid only for partitioned tables, specifies how many partitions are active for this table, where expression is one of the following:

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

For details on usage, see Active and Inactive Partitions 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. It also moves all IDENTITY and AUTO_INCREMENT columns to the destination schema. For details, see Moving Tables to Another Schema in the Administrator's Guide.

SET STORAGE load‑method

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 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.


Non-superusers must own the table they wish to change. They must also have USAGE privileges on the table schema for the following changes:

To move a table to another schema, non-superusers must have USAGE privilege on the current schema and CREATE privilege on the destination schema.

Exclusive ALTER TABLE Clauses

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


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;

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.