# ALTER COLUMN

The `ALTER COLUMN` statement is part of `ALTER TABLE` and sets, changes, or drops a column's `DEFAULT` constraint or drops the `NOT NULL` constraint.

Note:

To manage other constraints, see `ADD CONSTRAINT` and `DROP CONSTRAINT`.

## Synopsis

## Required privileges

The user must have the `CREATE` privilege on the table.

## Parameters

Parameter | Description
--- | ---
`table_name` | The name of the table with the column you want to modify.
`column_name` | The name of the column you want to modify.
`a_expr` | The new Default Value you want to use.

## Viewing schema changes

Whenever you initiate a schema change, CockroachDB registers it as a job, which you can view with `SHOW JOBS`.

## Examples

### Set or change a `DEFAULT` value

Setting the `DEFAULT` value constraint inserts the value when data's written to the table without explicitly defining the value for the column. If the column already has a `DEFAULT` value set, you can use this statement to change it.

The below example inserts the Boolean value `true` whenever you inserted data to the `subscriptions` table without defining a value for the `newsletter` column.

    > ALTER TABLE subscriptions ALTER COLUMN newsletter SET DEFAULT true;

### Remove `DEFAULT` constraint

If the column has a defined `DEFAULT` value, you can remove the constraint, which means the column will no longer insert a value by default if one is not explicitly defined for the column.

    > ALTER TABLE subscriptions ALTER COLUMN newsletter DROP DEFAULT;

### Remove `NOT NULL` constraint

If the column has the `NOT NULL` constraint applied to it, you can remove the constraint, which means the column becomes optional and can have NULL values written into it.

    > ALTER TABLE subscriptions ALTER COLUMN newsletter DROP NOT NULL;

### Convert a computed column into a regular column

New in v2.1: You can convert a stored, computed column into a regular column by using `ALTER TABLE`.

In this example, create a simple table with a computed column:

    > CREATE TABLE office_dogs (
        id INT PRIMARY KEY,
        first_name STRING,
        last_name STRING,
        full_name STRING AS (CONCAT(first_name, ' ', last_name)) STORED
      );

Then, insert a few rows of data:

    > INSERT INTO office_dogs (id, first_name, last_name) VALUES
        (1, 'Petee', 'Hirata'),
        (2, 'Carl', 'Kimball'),
        (3, 'Ernie', 'Narayan');

    > SELECT * FROM office_dogs;

    +----+------------+-----------+---------------+
    | id | first_name | last_name |   full_name   |
    +----+------------+-----------+---------------+
    |  1 | Petee      | Hirata    | Petee Hirata  |
    |  2 | Carl       | Kimball   | Carl Kimball  |
    |  3 | Ernie      | Narayan   | Ernie Narayan |
    +----+------------+-----------+---------------+
    (3 rows)

The `full_name` column is computed from the `first_name` and `last_name` columns without the need to define a view. You can view the column details with the `SHOW COLUMNS` statement:

    > SHOW COLUMNS FROM office_dogs;

    +-------------+-----------+-------------+----------------+------------------------------------+-------------+
    | column_name | data_type | is_nullable | column_default |       generation_expression        |   indices   |
    +-------------+-----------+-------------+----------------+------------------------------------+-------------+
    | id          | INT       |    false    | NULL           |                                    | {"primary"} |
    | first_name  | STRING    |    true     | NULL           |                                    | {}          |
    | last_name   | STRING    |    true     | NULL           |                                    | {}          |
    | full_name   | STRING    |    true     | NULL           | concat(first_name, ' ', last_name) | {}          |
    +-------------+-----------+-------------+----------------+------------------------------------+-------------+
    (4 rows)

Now, convert the computed column (`full_name`) to a regular column:

    > ALTER TABLE office_dogs ALTER COLUMN full_name DROP STORED;

Check that the computed column was converted:

    > SHOW COLUMNS FROM office_dogs;

    +-------------+-----------+-------------+----------------+-----------------------+-------------+
    | column_name | data_type | is_nullable | column_default | generation_expression |   indices   |
    +-------------+-----------+-------------+----------------+-----------------------+-------------+
    | id          | INT       |    false    | NULL           |                       | {"primary"} |
    | first_name  | STRING    |    true     | NULL           |                       | {}          |
    | last_name   | STRING    |    true     | NULL           |                       | {}          |
    | full_name   | STRING    |    true     | NULL           |                       | {}          |
    +-------------+-----------+-------------+----------------+-----------------------+-------------+
    (4 rows)

The computed column is now a regular column and can be updated as such:

    > INSERT INTO office_dogs (id, first_name, last_name, full_name) VALUES (4, 'Lola', 'McDog', 'This is not computed');

    > SELECT * FROM office_dogs;

    +----+------------+-----------+----------------------+
    | id | first_name | last_name |      full_name       |
    +----+------------+-----------+----------------------+
    |  1 | Petee      | Hirata    | Petee Hirata         |
    |  2 | Carl       | Kimball   | Carl Kimball         |
    |  3 | Ernie      | Narayan   | Ernie Narayan        |
    |  4 | Lola       | McDog     | This is not computed |
    +----+------------+-----------+----------------------+
    (4 rows)

## See also

- Constraints
- `ADD CONSTRAINT`
- `DROP CONSTRAINT`
- `ALTER TABLE`