ALTER TABLE columns ​

Column operations change a table's schema and validate the change against existing rows and dependencies.

Syntax ​

ALTER TABLE table ADD COLUMN column type [constraints]
ALTER TABLE table DROP COLUMN column
ALTER TABLE table RENAME COLUMN old_name TO new_name

Adding a column ​

An added column must have an allowed definition that also works for rows already in the table. Supply a suitable DEFAULT when existing rows need a value, particularly with NOT NULL.

Dropping or renaming a column ​

Dependent constraints, indexes, views and triggers can prevent an incompatible change. Renaming a column changes its schema name; use the new name in later statements.

ALTER COLUMN TYPE and ClickHouse MODIFY COLUMN are not supported. To change the value type, create a replacement table and use INSERT ... SELECT CAST(...).

Examples ​

Adding and renaming a field ​

CREATE TABLE profiles(id INTEGER PRIMARY KEY);
INSERT INTO profiles VALUES (1);
ALTER TABLE profiles ADD COLUMN label String DEFAULT 'new';
ALTER TABLE profiles RENAME COLUMN label TO status;

SELECT id, status
FROM profiles
FORMAT csv;

1,"new"

Dropping an unused field ​

ALTER TABLE profiles ADD COLUMN note String;
ALTER TABLE profiles DROP COLUMN note;

SELECT id, status
FROM profiles
FORMAT csv;

1,"new"

See CREATE TABLE for column definitions and constraints.