Modify existing table definitions.

​
Synopsis

ALTER TABLE table_name

  action [, ... ]



where action is one of:

  SET READY

  ADD [ COLUMN ] column_name data_type [ column_constraint [ ... ] ] [ INDEX [ index_parameters ] ]



where column_constraint is:

{ NULL |

  NOT NULL |

  DEFAULT default_expr |

  UNIQUE |

  PRIMARY KEY |

  REFERENCES reftable [ (refcolumn) ] }



and index_parameters is:

WITH ( storage_parameter [= value] [, ... ] )

​
Description

ALTER TABLE changes the metadata definition of an existing table in the current database.

The table must be in the NOT READY state, otherwise the command results in an error. A table is in NOT READY state if it’s created with the CREATE TABLE SET NOT READY option, and until the ALTER TABLE SET READY command is performed on it.

​
Parameters

table_name

The name of an existing table to alter.

​
action

SET READY

This form finalizes the table’s schema and index set, and makes it accessible for DML operations. Any subsequent ALTER TABLE command will fail as the table transitions to the READY state.

ADD [ COLUMN ]

Adds a new column to the table, using the same syntax as in CREATE TABLE. The column name must be unique within the table and conform with the Identifiers Limitations.

column_name

The name of the added column.

data_type

Data type of the new column. For more information on the supported data types, refer to Data Types.

The rest of the parameters used in this command are described in the CREATE TABLE command.

​
Examples

Create a table as not ready and alter it to modify its definition:

CREATE TABLE SET NOT READY parts WITH (devices = (m1d2, m2d5, m3d3));



ALTER TABLE parts

  ADD COLUMN p_partkey INT PRIMARY KEY INDEX WITH (devices = (m1d2)),

  ADD COLUMN p_name VARCHAR(40) NOT NULL UNIQUE INDEX

    WITH (devices = (m1d2, m2d5, m3d3), range_separators = ('j', 'p'));



ALTER TABLE parts

  ADD COLUMN p_date DATE NOT NULL ;



ALTER TABLE parts SET READY;

This example creates a table and builds it gradually with ALTER TABLE commands, and eventually finalizes it with SET READY.

Note that the ALTER TABLE command can only work as long as the parts table is defined as NOT READY.

​
Compatibility

The ALTER TABLE command conforms to the SQL standard, with exceptions listed below.

  1. SET READY is a Regatta extension
  2. WITH clause is a Regatta extension; storage parameters are not in the standard.