ALTER COLUMN

On this page Carat arrow pointing down

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.

Tip:

New in v19.1: 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

ALTER TABLE IF EXISTS table_name ALTER COLUMN column_name SET DEFAULT a_expr DATA TYPE typename COLLATE collation_name USING a_expr DROP DEFAULT NOT NULL STORED TYPE typename COLLATE collation_name USING a_expr

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

This schema change statement is registered as a job. You can view long-running jobs 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.

icon/buttons/copy
> 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.

icon/buttons/copy
> 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.

icon/buttons/copy
> 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:

icon/buttons/copy
> 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:

icon/buttons/copy
> INSERT INTO office_dogs (id, first_name, last_name) VALUES
    (1, 'Petee', 'Hirata'),
    (2, 'Carl', 'Kimball'),
    (3, 'Ernie', 'Narayan');
icon/buttons/copy
> 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:

icon/buttons/copy
> 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:

icon/buttons/copy
> ALTER TABLE office_dogs ALTER COLUMN full_name DROP STORED;

Check that the computed column was converted:

icon/buttons/copy
> 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:

icon/buttons/copy
> INSERT INTO office_dogs (id, first_name, last_name, full_name) VALUES (4, 'Lola', 'McDog', 'This is not computed');
icon/buttons/copy
> 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


Yes No
On this page

Yes No