Is "alter table" used to alter a column to be computed?

Yes, the ALTER TABLE statement is indeed used in SQL to alter a column to be a computed column (also sometimes called a generated column). A computed column is a column whose values are derived from other columns or expressions. The specific syntax can vary slightly depending on the database system (such as MySQL, PostgreSQL, SQL Server, etc.), but the general principle is similar.

Here's a breakdown of how this works and examples in different database systems:

General Concept:

You use ALTER TABLE to modify the table's structure. To turn a regular column into a computed one, you'll usually add a clause that specifies the computation rule. If the column already exists as a regular column, you'll modify it by dropping the old constraints and specifying the computation expression instead.

Examples:

1. SQL Server:

-- Assuming you have a table named "Products" with "Price" and "TaxRate"
-- Example: Adding a computed column "TotalPrice"
ALTER TABLE Products
ADD TotalPrice AS (Price (1 + TaxRate));

In SQL Server, the computed column is defined by AS (expression).

2. MySQL:

-- Assuming you have a table named "Orders" with "Quantity" and "UnitPrice"
-- Example: Adding a generated column "TotalAmount"
ALTER TABLE Orders
ADD COLUMN TotalAmount DECIMAL(10, 2) GENERATED ALWAYS AS (Quantity UnitPrice);

In MySQL, you use GENERATED ALWAYS AS (expression). The ALWAYS keyword indicates that the column is always computed and cannot be directly modified.

3. PostgreSQL:

-- Assuming you have a table named "Employees" with "Salary" and "Bonus"
-- Example: Adding a generated column "TotalCompensation"
ALTER TABLE Employees
ADD COLUMN TotalCompensation NUMERIC GENERATED ALWAYS AS (Salary + Bonus) STORED;

In PostgreSQL, use GENERATED ALWAYS AS (expression) STORED. The STORED keyword signifies that the value is pre-computed and stored on disk.

4. SQLite:

-- Assuming you have a table named "Items" with "Cost" and "Markup"
-- Example: Adding a generated column "SellingPrice"
ALTER TABLE Items
ADD COLUMN SellingPrice NUMERIC GENERATED ALWAYS AS (Cost (1 + Markup)) VIRTUAL;

In SQLite, VIRTUAL means that the value is computed each time it is read, and it’s not physically stored.

Important Considerations:

- The exact syntax and supported features may vary slightly among different database systems.

- Computed columns typically cannot be directly updated, as their values are derived from the expression.

- Ensure that the data types of the computed column and the result of the expression are compatible.

In conclusion, ALTER TABLE is indeed the command you use to modify a column to become a computed or generated column in most SQL databases, although the specific implementation and keywords (e.g., GENERATED, AS, STORED, VIRTUAL) can differ based on the database system.

Learn more about this