Alter table to modify default value of column
ALTER TABLE {TABLE NAME}
ALTER COLUMN {COLUMN NAME} SET DEFAULT '{DEFAULT VALUES}'
example :
ALTER TABLE RESULT
ALTER COLUMN STATUS SET DEFAULT 'FAIL'
ALTER TABLE *table_name*
MODIFY *column_name* DEFAULT *value*;
worked in Oracle
e.g:
ALTER TABLE MY_TABLE
MODIFY MY_COLUMN DEFAULT 1;
Following Justin's example, the command below works in Postgres:
alter table foo alter column col2 set default 'bar';
Your belief about what will happen is not correct. Setting a default value for a column will not affect the existing data in the table.
I create a table with a column col2
that has no default value
SQL> create table foo(
2 col1 number primary key,
3 col2 varchar2(10)
4 );
Table created.
SQL> insert into foo( col1 ) values (1);
1 row created.
SQL> insert into foo( col1 ) values (2);
1 row created.
SQL> insert into foo( col1 ) values (3);
1 row created.
SQL> select * from foo;
COL1 COL2
---------- ----------
1
2
3
If I then alter the table to set a default value, nothing about the existing rows will change
SQL> alter table foo
2 modify( col2 varchar2(10) default 'foo' );
Table altered.
SQL> select * from foo;
COL1 COL2
---------- ----------
1
2
3
SQL> insert into foo( col1 ) values (4);
1 row created.
SQL> select * from foo;
COL1 COL2
---------- ----------
1
2
3
4 foo
Even if I subsequently change the default again, there will still be no change to the existing rows
SQL> alter table foo
2 modify( col2 varchar2(10) default 'bar' );
Table altered.
SQL> select * from foo;
COL1 COL2
---------- ----------
1
2
3
4 foo
SQL> insert into foo( col1 ) values (5);
1 row created.
SQL> select * from foo;
COL1 COL2
---------- ----------
1
2
3
4 foo
5 bar