ALTER TABLE: Changing a Table

Sometimes you need a column that you did not plan for. ALTER TABLE changes an existing table, and the rows in it stay. For the examples below, employees has four rows.

Adding a column

ALTER TABLE employees ADD email text;

SELECT * FROM employees;

Output:

 id | age | department | email | gender | name
----+-----+------------+-------+--------+-------
  1 |  28 |         HR |  null | female | Alice
  2 |  32 |         IT |  null |   male |   Bob
  4 |  30 |  Marketing |  null |   male | David
  3 |  25 |      Sales |  null |   male |  John

(4 rows)

The rows that were already there show null in the new column. They get a value only when you write one.

You can add several columns at once. Put them in parentheses:

ALTER TABLE employees
ADD (phone text, city text);

Dropping a column

ALTER TABLE employees DROP phone;

The column and all its values are deleted. There is no undo. Even if you add a column with the same name again, the old values do not come back.

What you can't change

Cassandra does not allow some changes. Here are three of them:

ALTER TABLE employees DROP id;
ALTER TABLE employees RENAME name TO full_name;
ALTER TABLE employees ALTER age TYPE bigint;

Output:

InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot drop PRIMARY KEY column id"
InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot rename non PRIMARY KEY column name"
InvalidRequest: Error from server: code=2200 [Invalid query] message="Altering column types is no longer supported"
  • You can't drop or replace the primary key. The key decides where each row is stored. A new key would mean moving every row, so you create a new table instead.
  • You can't rename normal columns. Only primary key columns can be renamed.
  • You can't change a column's type. Add a new column with a new name and the type you want, then copy the values into it.

Choices about the primary key are permanent. That is why Cassandra tables are planned carefully at the start, and the Primary Keys section explains how.

Now add an email column to the employees table in the exercise.