ALTER TABLE

Rename or add a new column to an existing table

Synopsis:

 ALTER TABLE [database-name .] table-name
  {RENAME TO new-table-name} | {ADD [COLUMN] column-def}

Description:

QDB's version of the ALTER TABLE command lets you add a new column to or rename an existing table. It isn't possible to remove a column from a table.

The RENAME TO syntax is used to rename the table identified by [database-name.]table-name to new-table-name. This command cannot be used to move a table between attached databases, only to rename a table within the same database.

If the table being renamed has triggers or indexes, then these remain attached to the table after it has been renamed. However, if there are any view definitions or statements executed by triggers that refer to the table being renamed, these are not automatically modified to use the new table name. If this is required, the triggers or view definitions must be dropped and recreated by hand to use the new table name.

The ADD [COLUMN] syntax is used to add a new column to an existing table. The new column is always appended to the end of the list of existing columns. The column-def may take any of the forms permissible in a CREATE TABLE statement, with the following restrictions:

The column may not have a PRIMARY KEY or UNIQUE constraint.
The column may not have a default value of CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.
If a NOT NULL constraint is specified, then the column must have a default value other than NULL.

The execution time of the ALTER TABLE command is independent of the amount of data in the table. The ALTER TABLE command runs as quickly on a table with 10 million rows as it does on a table with one row.

Note: After ADD COLUMN has been run on a database, that database will not be readable by QDB until the database is cleaned up with the VACUUM statement.