# ALTER COLUMN

The `ALTER COLUMN` statement is part of `ALTER TABLE` and can be used to:

- Set, change, or drop a column's `DEFAULT` constraint
- Set or drop a column's `NOT NULL` constraint
- Increase the precision of the column's data type

Note:

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

Tip:

This command can be combined with other `ALTER TABLE` commands in a single statement. For a list of commands that can be combined, see `ALTER TABLE`. For a demonstration, see Add and rename columns atomically.

## 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.
`SET DEFAULT a_expr` | The new Default Value you want to use.
`typename` | The new, altered type you want to use. In CockroachDB versions < v20.2, support for altering column types is limited to increasing the precision of the current column type. For details, see Altering column types.

## Viewing schema changes

This schema change statement is registered as a job. You can view long-running jobs with `SHOW JOBS`.

## Altering column types

In CockroachDB versions < v20.2, support for altering column types is limited to increasing the precision of the current type of a column. You cannot convert the column type to another data type, or decrease the precision of the column type. Changing the column type from its current type to the same type and precision will result in a no-op, with no error.

You can use `ALTER COLUMN TYPE` if the following conditions are met:

- The on-disk representation of the column remains unchanged. For example, you cannot change the column data type from `STRING` to an `INT`.
- The existing data remains valid. For example, you can change the column data type from `STRING[10]` to `STRING[20]`, but not to `STRING [5]` since that will invalidate the existing data.

The following are equivalent in CockroachDB:

- `ALTER TABLE ... ALTER ... TYPE`
- `ALTER TABLE ... ALTER COLUMN TYPE`
- `ALTER TABLE ... ALTER COLUMN SET DATA TYPE`

For an example of `ALTER COLUMN TYPE`, see Increase a column type's precision.

## 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;

### Set `NOT NULL` constraint

Setting the `NOT NULL` constraint specifies that the column cannot contain `NULL` values.

    > ALTER TABLE subscriptions ALTER COLUMN newsletter SET NOT NULL;

### 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

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)

### Increase a column type's precision

The TPC-C database contains a `customer` table with a column `c_credit_lim` of type `DECIMAL(10,2)`. Suppose you want to increase the precision of the column's data type to `DECIMAL (12,2)`:

    > ALTER TABLE customer ALTER c_credit_lim type DECIMAL (12,2);

    ALTER TABLE

    Time: 80.814044ms

## See also

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