Add new column with default value, not for existing rows

Question

alter table report add favourite_film VARCHAR2(100) DEFAULT 'Star Wars';

This adds a new column with default value, but it appears to me it also sets the default value on all pre-existing rows rather than leaving them null which is my preference. We want to add a new column with a default value for convenience, but rows before this column was added should have a null value.

Is there a better way to do this than simply setting the value of all rows to null immediately after adding the new column?

Answer 1

Try doing something like:

SQL> create table testtab
(
id number,
col1 varchar2(10)
)
Table created.
SQL> insert into testtab(id, col1) values (1,'TEST1')
1 row created.
SQL> insert into testtab(id, col1) values (2,'TEST2')
1 row created.
SQL> commit
Commit complete.
SQL> alter table testtab add col2 varchar2(10)
Table altered.
SQL> alter table testtab modify col2 default 'DEFAULT'
Table altered.
SQL> select * from testtab

        ID COL1       COL2      
---------- ---------- ----------
         1 TEST1                
         2 TEST2                

2 rows selected.
SQL> insert into testtab(id, col1) values (3,'TEST3')
1 row created.
SQL> select * from testtab

        ID COL1       COL2      
---------- ---------- ----------
         1 TEST1                
         2 TEST2                
         3 TEST3      DEFAULT   

3 rows selected.

Note that per the Oracle docs, if you use the add column clause of alter table command, it will add the specified default value to existing rows:

If you specify the DEFAULT clause for a nullable column, then the default value is added to existing rows as part of this ALTER TABLE statement, and any update triggers defined on the table are fired. This behavior also results if you change a NOT NULL column with a default value to be nullable.