I wish to increase decimal precision and scale for a decimal column.
I am aware that I can drop the column, and re-create it, but doing so will mean losing the data in the column.
Is there a way using Laravel Schema::table that I can alter the precision and scale of the column without dropping it?
e.g. something like:
Schema::table('prices', function(Blueprint $t) {
$t->buy_price->decimal(5,2);
});
this worked for me:
public function up()
{
Schema::table('prices', function(Blueprint $t) {
$t->decimal('buy_price', 5, 2)->change();
});
}
when rolling back use original precision values of 3, 1 instead
public function down()
{
Schema::table('prices', function(Blueprint $t) {
$t->decimal('buy_price', 3, 1)->change();
});
}
I avoid DB specific "raw" statements as they might fail when I change to another DBMS engine. So I let Laravel handle necessary syntax when working w/DB
Just create another migration and in the up method add following code:
public function up()
{
// Change db_name and table_name
DB::select(DB::raw('ALTER TABLE `db_name`.`table_name` CHANGE COLUMN `buy_price` `buy_price` decimal(10,2) NOT NULL;'));
}
Also in the down method just set the old value so you can roll-back:
public function down()
{
// Change db_name and table_name
DB::select(DB::raw('ALTER TABLE `db_name`.`table_name` CHANGE COLUMN `buy_price` `buy_price` decimal(5,2) NOT NULL;'));
}
Then migrate as usual from the terminal/command prompt using php artisan migrate.
Didn't work for me, using select gives me a General Error: 2053 because select expects aa array to be returned. I'm not sure if it's the version of MySQL, or a windows/linux thing.
I had to use DB:statement instead:
public function up()
{
// Change db_name and table_name
DB::statement(DB::raw('ALTER TABLE `db_name`.`table_name` CHANGE COLUMN `buy_price` `buy_price` decimal(10,2) NOT NULL;'));
}
and
public function down()
{
// Change db_name and table_name
DB::statement(DB::raw('ALTER TABLE `db_name`.`table_name` CHANGE COLUMN `buy_price` `buy_price` decimal(5,2) NOT NULL;'));
}
Hope this helps anyone who comes across this.
