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?

alter table with null value default or update the new field to null after alter table — Frank
– Frank, Commented Sep 14, 2017 at 14:15
So you want the default value to be 'Star Wars', but only for new entries to the table, after adding the new column? More info/code would be needed to understand the logic of your program, but it seems that the first step would be to remove the DEFAULT 'Star Wars' part of the alter table statement, and then determine how to make 'Star Wars' the default for new entries in the table. — Jim Newpower
– Jim Newpower, Commented Sep 14, 2017 at 14:18
Well yes, that's what I was asking (at least I thought I was) — Mr. Boy
– Mr. Boy, Commented Sep 14, 2017 at 14:33

tbone · Accepted Answer · 2017-09-14 14:47:07Z

6

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.

edited Sep 14, 2017 at 14:47

answered Sep 14, 2017 at 14:23

tbone

15.5k3 gold badges37 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mr. Boy Over a year ago

That's interesting. Why would modifying the default have different behaviour out of interest?

Collectives™ on Stack Overflow

Add new column with default value, not for existing rows

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related