ALTER TABLE

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

Parameters

[database.]schema

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

myschema.thisDbObject

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 
[column‑constraint]
[ENCODING encoding‑type]
[PROJECTIONS (projections-list) | ALL PROJECTIONS]

The optional clause IF NOT EXISTS 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:
    [CONSTRAINT constraint-name]
      { {NULL | NOT NULL} | DEFAULT expression }
    
  • 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.

See Constraints in the Administrator's Guide.

ALTER COLUMN

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.

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

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

ALTER CONSTRAINT constraint‑name {ENABLED | DISABLED}

See Constraint Enforcement in the Administrator's Guide.

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 specifies to generate 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.

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.
{ INCLUDE
| EXCLUDE 
| MATERIALIZE }
[SCHEMA] PRIVILEGES

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
  • 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 ] [ 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. See Renaming Columns in the Administrator's Guide.

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 ActivePartitionCount expression

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.

SET SCHEMA

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.

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.

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:

Schema privileges required… For these operations…
CREATE, USAGE Rename table

CREATE: destination schema
USAGE: current schema

Move table to another schema

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]

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.