ALTER TABLE

The schema changes ALTER TABLE makes — columns, attribute groups, defaults, nullability, names and types — and how running readers follow them.

Every ALTER TABLE creates a new schema generation of the table. Existing rows are not rewritten: they are read by column identity, so a column added later reads as its introduction value (its default, or NULL) on older rows. Readers of the table, including running transformations, keep working across changes.

Actions

Action	Form
add a column	`ALTER TABLE <t> ADD [COLUMN] <column definition>`
drop a column	`ALTER TABLE <t> DROP [COLUMN] <c>`
change a column type	`ALTER TABLE <t> ALTER [COLUMN] <c> TYPE <type>`
add an attribute group	`ALTER TABLE <t> ADD ATTRIBUTE GROUP <g> (<column> <type> NULL, …) [VALID BY (…)] [REVISION BY (…)]`
rename a column	`ALTER TABLE <t> RENAME COLUMN <old> TO <new>`
rename a table	`ALTER TABLE <t> RENAME TO <new>`
set a default	`ALTER TABLE <t> ALTER [COLUMN] <c> SET DEFAULT <literal>`
remove a default	`ALTER TABLE <t> ALTER [COLUMN] <c> DROP DEFAULT`
make a column nullable	`ALTER TABLE <t> ALTER [COLUMN] <c> DROP NOT NULL`

One statement carries one action. The column definition of ADD COLUMN takes the same options as in CREATE TABLE.

Adding columns

ALTER TABLE transfers ADD COLUMN fee UINT256 NULL;

ALTER TABLE balances ADD COLUMN label STRING NOT NULL DEFAULT 'unlabelled';

Every existing row reads the new column at its introduction value: NULL for fee, 'unlabelled' for label.

Defaults and nullability

ALTER TABLE balances ALTER COLUMN label SET DEFAULT 'wallet';

ALTER TABLE balances ALTER COLUMN label DROP NOT NULL;

A new default applies to later writes; rows written before keep the value they were written with.

Renaming

ALTER TABLE transfers RENAME COLUMN fee TO network_fee;

Rows of every generation read under the new name, including reads AS OF COMMIT an earlier commit.

Adding attribute groups

ALTER TABLE balances ADD ATTRIBUTE GROUP labels (
  label_text STRING NULL,
  labelled_at DATETIME64(3, 'UTC') NULL
);

The group's columns are nullable. A new producer or transformation can own the group at once. See Attribute groups.

Changes under running transformations

A transformation that reads or writes a table is re-bound to each new schema generation and keeps running. A table keeps up to 64 schema generations, configurable with node.resources.execution.schema_maximum_rungs. See Limits.