ALTER TABLE

Modifies the metadata of an existing table.

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

Syntax

ALTER TABLE [[{ namespace. | 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 [namespace.]schema }
}

Note

Several ALTER TABLE clauses cannot be specified with other clauses in the same statement (see Exclusive ALTER TABLE Clauses below). Otherwise, ALTER TABLE supports multiple comma-delimited clauses. For example, the following ALTER TABLE statement changes the my_table table in two ways: reassigns ownership to Joe, and sets a UNIQUE constraint on the b column:

=> ALTER TABLE my_table OWNER TO Joe,
   ADD CONSTRAINT unique_b UNIQUE (b) ENABLED;

Arguments

namespace

For Eon Mode databases, namespace that contains the table to alter. If no namespace is specified, the namespace of the table is assumed to be default_namespace.

database

For Enterprise Mode databases, name of the database containing the table. If specified, it must be the current database.

schema

Name of the schema that contains the table to alter.

table

The table to alter.

ADD COLUMN

Adds one or more columns 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.

Adding a column with the same name as one that already exists is an error unless you use IF NOT EXISTS.

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

A column constraint:
```
  [ CONSTRAINT constraint-name ]
  { [NOT] NULL 
    | DEFAULT expression 
    | SET USING expression 
    | DEFAULT USING expression }
```
Columns that use DEFAULT with static values can be added in a single ALTER TABLE statement. Columns that use non-static DEFAULT values must be added in separate ALTER TABLE statements.
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.
Note
If inherited privileges are disabled at the database level, schema privileges can still be materialized.

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 expression
  [ GROUP BY 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 [namespace.]schema-name moves the table from its current schema to [namespace.]schema-name. For Eon Mode databases, the new schema must be in the same namespace as the old schema. When no namespace is provided, the namespace is set to default_namespace.

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.
Complex type columns cannot use DEFAULT or SET USING.
Expressions returning complex types cannot be used as projection columns, and projections cannot be segmented or ordered by columns of complex types.

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.

Examples

In an Eon Mode database, the following statement alters the schema of the flights table from schema airline1 in namespace airport to schema airline2 in namespace airport:

=> ALTER TABLE airport.airline1.flights SET SCHEMA airpot.airline2.flights;

ALTER TABLE

Syntax

Note

Arguments

Note

Privileges

Restrictions for complex types

Exclusive ALTER TABLE clauses

Node down limitations

Pre-aggregated projection restrictions

External table restrictions

Locked tables

Examples

See also

In this section