Altering a computed column

Change or remove the expression used in a computed column.

Prérequis

You must be the owner of the table, or have one of the following privileges:

ALTER privilege on the table along with one of COMMENT ANY OBJECT, CREATE ANY OBJECT, or CREATE ANY TABLE system privileges
ALTER ANY TABLE system privilege
ALTER ANY OBJECT system privilege

Task

Connect to the database.

Execute an ALTER TABLE statement similar to the following to change the expression used for a computed column:

ALTER TABLE table-name
ALTER column-name
SET COMPUTE ( new-expression );

To convert a column to a regular (non-computed) column, execute an ALTER TABLE statement similar to the following:
ALTER TABLE table-name ALTER column-name DROP COMPUTE;

Résultat

In the case of changing the computation for the column, the column is recalculated when this statement is executed.

In the case of a computed column being changed to be a regular (non-computed) column, existing values in the column are not changed when the statement is executed, and are not automatically updated thereafter.

Exemple

Create a table named alter_compute_test, populate it with data, and run a select query on the table by executing the following statements:

CREATE TABLE alter_compute_test (
   c1 INT,
   c2 INT
) ;
INSERT INTO alter_compute_test (c1) VALUES(100);
SELECT * FROM alter_compute_test ;

Notice that column c2 returns a NULL value. Alter column c2 to become a computed column, populate the column with data, and run another SELECT statement on the alter_compute_test table.

ALTER TABLE alter_compute_test
   ALTER c2
   SET COMPUTE ( DAYS ( '2001-01-01' , CURRENT DATE ) )
INSERT INTO alter_compute_test (c1) VALUES(200) ;
SELECT * FROM alter_compute_test ;

The column c2 now contains the number of days since 2001-01-01. Next, alter column c2 so that it is no longer a computed column:

ALTER TABLE alter_compute_test
ALTER c2
DROP COMPUTE ;

Altering a computed column

Task

See also