Problem
When I tried to change the data type of a column and set a new default value, I received the following error:
ALTER TABLE foobar_data ALTER COLUMN col VARCHAR(255) NOT NULL SET DEFAULT '{}';
Asked by qazwsx
Solution #1
ALTER TABLE foobar_data MODIFY COLUMN col VARCHAR(255) NOT NULL DEFAULT '{}';
A second option (thanks to juergen d) that accomplishes the same goal:
ALTER TABLE foobar_data CHANGE COLUMN col col VARCHAR(255) NOT NULL DEFAULT '{}';
Answered by fancyPants
Solution #2
The ALTER.. SET syntax can be used. Just don’t put everything else in there. If you want to add the rest of the column definition, follow the accepted response and use the MODIFY or CHANGE syntax.
Anyway, here’s the ALTER syntax for establishing a column default (because that’s what I came here to do):
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT 'literal';
‘Literal’ could also refer to a number (e.g. …SET DEFAULT 0). I’ve never tried it with… Why not use SET DEFAULT CURRENT TIMESTAMP instead?
Answered by DaveJenni
Solution #3
This works for me when I want to add a default value to a column that has already been created:
ALTER TABLE Persons
ALTER credit SET DEFAULT 0.0;
Answered by Akash Dole
Solution #4
For DEFAULT CURRENT_TIMESTAMP:
ALTER TABLE tablename
CHANGE COLUMN columnname1 columname1 DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
CHANGE COLUMN columnname2 columname2 DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
It’s worth noting that there’s a double columnname declaration.
Removing DEFAULT CURRENT_TIMESTAMP:
ALTER TABLE tablename
ALTER COLUMN columnname1 DROP DEFAULT,
ALTER COLUMN columnname2 DROPT DEFAULT;
Answered by Leonard Lepadatu
Solution #5
Accepted Answer performs admirably.
In the event of an Invalid usage of NULL value error, update all null values in that column to the default value before attempting the modify.
UPDATE foobar_data SET col = '{}' WHERE col IS NULL;
ALTER TABLE foobar_data MODIFY COLUMN col VARCHAR(255) NOT NULL DEFAULT '{}';
Answered by Pradeep Kumar
Post is based on https://stackoverflow.com/questions/11312433/how-to-alter-a-column-and-change-the-default-value