ALTER TABLE

The ALTER TABLE statement changes the schema of an existing table in the catalog.

RENAME TABLE

Query

ALTER TABLE integersRENAME TO integers_old;

The RENAME TO clause renames an entire table, changing its name in the schema. Note that any views that rely on the table are not automatically updated.

Tip

ALTER TABLE changes the schema of an existing table.

RENAME COLUMN

To rename a column of a table, use the RENAME or RENAME COLUMN clauses:

Query

ALTER TABLE integersRENAME COLUMN i TO j;

Query

ALTER TABLE integersRENAME i TO j;

The RENAME [COLUMN] clause renames a single column within a table. Any constraints that rely on this name (e.g., CHECK constraints) are automatically updated. However, note that any views that rely on this column name are not automatically updated.

ADD COLUMN

To add a column of a table, use the ADD or ADD COLUMN clauses.

E.g., to add a new column with name k to the table integers, it will be filled with the default value NULL:

Query

ALTER TABLE integersADD COLUMN k INTEGER;

Or:

Query

ALTER TABLE integersADD k INTEGER;

Add a new column with name l to the table integers, it will be filled with the default value 10:

Query

ALTER TABLE integersADD COLUMN l INTEGER DEFAULT 10;

The ADD [COLUMN] clause can be used to add a new column of a specified type to a table. The new column will be filled with the specified default value, or NULL if none is specified.

DROP COLUMN

To drop a column of a table, use the DROP or DROP COLUMN clause:

E.g., to drop the column k from the table integers:

Query

ALTER TABLE integersDROP COLUMN k;

Or:

Query

ALTER TABLE integersDROP k;

The DROP [COLUMN] clause can be used to remove a column from a table. Note that columns can only be removed if they do not have any indexes that rely on them. This includes any indexes created as part of a PRIMARY KEY or UNIQUE constraint. Columns that are part of multi-column check constraints cannot be dropped either. If you attempt to drop a column with an index on it, SereneDB returns a Catalog Error reporting that the column is referenced by that index or constraint.

[SET [DATA]] TYPE

Change the type of the column i to the type VARCHAR using a standard cast:

Query

ALTER TABLE integersALTER i TYPE VARCHAR;

Instead of ALTER ⟨column_name⟩ TYPE ⟨type⟩, you can also use the equivalent ALTER ⟨column_name⟩ SET TYPE ⟨type⟩ and the ALTER ⟨column_name⟩ SET DATA TYPE ⟨type⟩ clauses.

Change the type of the column i to the type VARCHAR, using the specified expression to convert the data for each row:

Query

ALTER TABLE integersALTER i SET DATA TYPE VARCHAR USING concat(i, '_', j);

The [SET [DATA]] TYPE clause changes the type of a column in a table. Any data present in the column is converted according to the provided expression in the USING clause, or, if the USING clause is absent, cast to the new data type. Note that columns can only have their type changed if they do not have any indexes that rely on them and are not part of any CHECK constraints.

Handling Structs

There are two options to change the sub-schema of a STRUCT-typed column.

ALTER TABLE with struct_insert

You can add fields to a STRUCT column with ALTER TABLE: give the new struct type in the TYPE clause and use struct_insert in the USING clause to transform the existing values. For example:

Query

CREATE TABLE tbl (col STRUCT(i INTEGER));
INSERT INTO tbl VALUES ({'i': 1});
ALTER TABLE tblALTER col TYPE STRUCT(i INTEGER, a INTEGER, b VARCHAR)USING struct_insert(col, a := 42, b := NULL::VARCHAR);
SELECT col FROM tbl;

Result

 col--------- (1,42,)

ALTER TABLE with ADD COLUMN / DROP COLUMN / RENAME COLUMN

SereneDB ALTER TABLE supports the ADD COLUMN, DROP COLUMN and RENAME COLUMN clauses to update the sub-schema of a STRUCT.

SET / DROP DEFAULT

The SET DEFAULT clause changes the default value of a column:

Query

ALTER TABLE integersALTER COLUMN i SET DEFAULT 10;

The DROP DEFAULT clause removes the default value of a column, resetting it to NULL:

Query

ALTER TABLE integersALTER COLUMN i DROP DEFAULT;

ADD PRIMARY KEY

The ADD PRIMARY KEY clause promotes one or more existing columns to the table's primary key. The chosen columns are made implicitly NOT NULL, and the constraint is enforced from that point on:

Query

ALTER TABLE integersADD PRIMARY KEY (i);

A primary key can also span multiple columns:

Query

ALTER TABLE integersADD PRIMARY KEY (i, j);

The statement fails if the table already has a primary key, if an index depends on the table or if the existing data would violate the new constraint (duplicate or NULL values in the key columns).

SET / RESET (Table Options)

Tip

The SET and RESET table-option clauses are not yet supported in SereneDB.

Attempting to set table options returns an error:

Query

ALTER TABLE my_tableSET ('option_name' = 'value');

Result

db error: ERROR: this ALTER TABLE operation is not supported

Attempting to reset table options returns an error:

Query

ALTER TABLE my_tableRESET ('option_name');

Result

db error: ERROR: this ALTER TABLE operation is not supported

Attempting to set or reset multiple options in a single statement returns an error:

Query

ALTER TABLE my_tableSET ('option1' = 'value1', 'option2' = 'value2');
ALTER TABLE my_tableRESET ('option1', 'option2');

Result

db error: ERROR: this ALTER TABLE operation is not supported
db error: ERROR: this ALTER TABLE operation is not supported

DROP CONSTRAINT

The DROP CONSTRAINT clause removes a named CHECK constraint from a table:

Query

ALTER TABLE itemsDROP CONSTRAINT positive_id;

DROP CONSTRAINT (and RENAME CONSTRAINT) operate on named CHECK constraints. Index-backed constraints — those created by PRIMARY KEY and UNIQUE — cannot be dropped this way.

ADD CONSTRAINT

The ADD CONSTRAINT clause adds a CHECK, UNIQUE or PRIMARY KEY constraint to an existing table:

Query

ALTER TABLE itemsADD CONSTRAINT positive_id CHECK (id > 0);

FOREIGN KEY constraints cannot be added with ADD CONSTRAINT.

Limitations

ALTER COLUMN fails if values of conflicting types have occurred in the table at any point, even if they have been deleted:

Query

CREATE TABLE tbl (col VARCHAR);
INSERT INTO tblVALUES ('asdf'), ('42');
DELETE FROM tblWHERE col = 'asdf';
ALTER TABLE tblALTER COLUMN col TYPE INTEGER;

Result

db error: ERROR: Conversion Error: Could not convert string 'asdf' to INT32
LINE 1: (varies)

Currently, this is expected behavior. As a workaround, you can create a copy of the table:

Query

CREATE TABLE tbl_copy AS FROM tbl;