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.

The table to alter.

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.

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

See Constraints in the Administrator's Guide.

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:

  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;

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.

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

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.

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

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.

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.

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.

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.

CREATE: destination schema
USAGE: current schema