ALTER TABLE

Modifies the metadata of an existing table. All 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 
[ NULL | NOT NULL ]
[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:
      {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.

See Constraints in the Administrator's Guide.

ALTER COLUMN {SET | DROP} expression

Sets or drops a setting for a column of a scalar data type (not ROW), as follows:

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

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;
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 Privilege 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. Vertica testing has shown that direct loads offer optimal performance in the widest range of scenarios.
  • TRICKLE: Loads data only into WOS, suitable for frequent incremental loads.

For databases created in Vertica releases ≥ 9.3, Vertica ignores load options and hints and defaults to a load method of DIRECT. You can configure this behavior with configuration parameter DMLTargetDirect.

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]

Node Down

In Enterprise mode, a number of ALTER TABLE operations are not supported when one or more 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

This restriction applies only to Enterprise mode; in Eon mode, it is ignored.

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.