Schema Evolution

When you create a table and later discover an error or a change in your application's requirements, you might consider dropping and recreating the table. However, this approach is not ideal if the table contains data or is referenced by other database objects, such as foreign key constraints. To address this, Tacnode provides a set of commands to modify existing tables. It's important to note that this process is different from altering the data within a table; the focus here is on changing the table's definition or structure.

You can perform the following operations:

• Adding a Column
• Removing a Column
• Adding a Constraint
• Removing a Constraint
• Changing a Column's Default Value
• Changing a Column's Type
• Renaming a Column
• Renaming a Table

All these operations are executed using the ALTER TABLE command. For detailed usage, refer to Modifying Tables. Below is the basic information.

Adding a Column

Assume the following command created the original table:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric
);

To add a column, use this command:

ALTER TABLE products ADD COLUMN description text;

New columns are initially filled with the default value (or NULL if no DEFAULT clause is specified).

• Note: If you add a column with a deterministic default value, Tacnode will avoid updating every row in the table when executing the ALTER TABLE statement. Instead, the column will return the default value the next time it is accessed, making ALTER TABLE fast, even on large tables.

• However, if the default value is non-deterministic (e.g., clock_timestamp()), ALTER TABLE will need to update every row in the table. Tacnode uses the Online Schema Change technique to prevent the table from being locked for an extended period, ensuring it remains readable and writable.

You can also set constraints on the columns using the standard syntax:

ALTER TABLE products ADD COLUMN description text CHECK (description <> '');

Most options applicable to the CREATE TABLE column can be used here. However, the default value must satisfy the specified constraints; otherwise, the addition will fail. After successfully adding the new column, you can add constraints later (as detailed below).

Removing a Column

To drop a column, use this command:

ALTER TABLE products DROP COLUMN description;

When a column is deleted, all associated data and any table constraints linked to it are also removed. However, if a foreign key constraint from another table references this column, Tacnode will not automatically remove that constraint. You should use CASCADE to delete all dependencies linked to the column.

ALTER TABLE products DROP COLUMN description CASCADE;

Adding a Constraint

To add a constraint, use the syntax designated for table constraints. For example:

ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

To apply a not null constraint that isn't defined as a table constraint, use this syntax:

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

Constraints are verified at the time of the statement. Therefore, the data in the table must meet the constraints before adding them; otherwise, the command will not succeed.

Removing a Constraint

To remove a constraint, you must know its name. If you assigned a name, this step is straightforward. If not, the system generates a name, which you must discover. You can use the psql command \d tablename for assistance; other interfaces might also offer ways to view table details. After that, execute the following command:

ALTER TABLE products DROP CONSTRAINT some_name;

When working with a generated constraint name such as $2, remember to enclose it in double quotes to ensure it is a valid identifier.

Like dropping a column, you must include CASCADE to remove something that relies on a constraint. For instance, a foreign key constraint depends on the referenced column's unique or primary key constraint.

The process is consistent for all types of constraints except for non-null constraints. To drop a non-null constraint, use the following command:

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

• A not-null constraint has no name.

Changing a Column's Default Value

You can set a new default value for a column using this command:

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

This does not affect the existing rows in the table; it only changes the default value for future INSERT operations.

To remove any default values, use the command below:

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

This is essentially equivalent to setting NULL as the default value. Consequently, if a default value isn’t specified, removing it isn’t considered an error since the default value defaults to NULL implicitly.

Changing a Column's Type

To change a column to a different data type, use the following command:

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

This operation will only succeed if every existing entry in the column can be converted to the new type through implicit conversion. You can use a USING clause to define how the new value is derived from the old one for more complex conversions.

Tacnode will attempt to convert the column's default value (if available) to the new type and any constraints related to the column. However, these conversions might fail or yield unexpected results. It is generally advisable to remove any constraints on the column before changing its type and reapply the adjusted constraints afterward.

Renaming a Column

To rename a column:

ALTER TABLE products RENAME COLUMN product_no TO product_number;

Renaming a Table

To rename a table:

ALTER TABLE products RENAME TO items;