Home / Questions / 192902
Accepted
MarianoC1993
Asked: 2020-08-31 12:40:20 +0800 CST 2020-08-31 12:40:20 +0800 CST

Add new value to an enum type field in Laravel

  • 772

I am working with migrations in Laravel and I have a partners table that has a type attribute of type enum with the following values:

$table->enum('type', ['cliente', 'proveedor', 'empleado', 'transportadora'])->nullable();

What I need is the following: Create another migration so that I can modify the partners table, adding one more value to the enum so that the database looks like this:

['cliente', 'proveedor', 'empleado', 'transportadora', 'nuevo_dato']

What I tried so far was the following:

Schema::table('partners', function ($table) {            
         DB::statement("ALTER COLUMN 'type' ADD VALUE 'nuevo_dato' AFTER 'cliente'");
});

And I also tried:

Schema::table('partners', function ($table) {    
      $table->enum('type', ['cliente', 'proveedor', 'empleado', 'transportadora', 'nuevo_dato'])->change();
});

Y...

DB::statement("ALTER TABLE 'partners' MODIFY COLUMN 'type' enum('nuevo_dato') NOT NULL AFTER `transportadora`");

Without reaching the expected result. I would be very grateful if you help me with this. (PS: I'm using PostgreSQL)

laravel

2 Answers

  1. Best Answer

    In the documentation it explains how to make the modifications using doctrine/dbal( Modifying Columns ) but clarifies that it is not supported for columns of type enum.

    I particularly try to avoid enum type columns and handle the options from the application, but I can think of a way to solve your problem that even if it is a few steps should solve your problem.

    1st Make a backup of the database.

    in migration 

    public function up()
{
    // Generá una nueva columna igual a `type`
    Schema::table('partners', function ($table) {    
          $table->enum('type2', ['cliente', 'proveedor', 'empleado', 'transportadora'])->nullable();
    });

    // Copiá los datos de la columna `type` a la columna `type2` 
    \DB::raw('UPDATE `partners` SET type2=type');

    // Eliminá la columna `type` y volvé a generarla con la nueva definición
    Schema::table('partners', function (Blueprint $table) {
        $table->dropColumn(['type']);
        $table->enum('type', ['cliente', 'proveedor', 'empleado', 'transportadora', 'nuevo_dato']);
    });

    // Volvé a copiar los datos  
    \DB::raw('UPDATE `partners` SET type=type2'); 

    // y luego eliminá la columna `type2` 
    Schema::table('partners', function (Blueprint $table) {
        $table->dropColumn([type2']);
    });
}

    The truth is that I never did something like this but it should work. I await your comment and remember to MAKE A BACKUP of the DB BEFORE doing anything, especially if you are in production. I recommend you to clone the base in your local environment and run the migration and verify that it works before running the migration in production.

    • 3

  2. The problem with enumis that they create a constraint on our table that limits us to using only the previously defined cases.
    So the first thing we need to do is remove the constraint:

     DB::statement("ALTER TABLE purchases DROP CONSTRAINT purchases_ref_check");

    We define the new data that we want to include in theenum

    $types = ['single', 'monthly', 'biannual', 'amount', 'other'];
$result = join(', ', array_map(function( $value ) { 
    return sprintf("'%s'::character varying", $value); 
}, $types));

    We will recreate the constraint

    DB::statement("ALTER TABLE purchases 
    add CONSTRAINT purchases_ref_check 
    CHECK (ref::text = ANY (ARRAY[$result]::text[]))"
);
    • 1