Update enum column in Laravel migration using PostgreSQL

Question

According to this answer, I have to run a raw query if I want to update an enum in MySQL. But with PostgreSQL, I can't use this query, and enum type for PostgreSQL in Laravel seems strange.

Is there any way to update enum in a migration for postgreSQL ?

Answer 1

Laravel use constraint on character varying for enum.

Assuming there is a table mytable with an enum column status, we have to drop the constraint (named tablename_columnname_check) then add it in a migration like this:

DB::transaction(function () {
    DB::statement('ALTER TABLE mytable DROP CONSTRAINT mytable_status_check;');
    DB::statement('ALTER TABLE mytable ADD CONSTRAINT mytable_status_check CHECK (status::TEXT = ANY (ARRAY[\'pending\'::CHARACTER VARYING, \'accepted\'::CHARACTER VARYING, \'canceled\'::CHARACTER VARYING]::TEXT[]))');
});

It solves the problem, hope it can help!

Answer 2

To extend on @rap-2-h's answer - here is a generic method you can use:

/**
 * Alter an enum field constraints
 * @param $table
 * @param $field
 * @param array $options
 */
protected function alterEnum($table, $field, array $options) {

    $check = "${table}_${field}_check";

    $enumList = [];

    foreach($options as $option) {
        $enumList[] = sprintf("'%s'::CHARACTER VARYING", $option);
    }

    $enumString = implode(", ", $enumList);

    DB::transaction(function () use ($table, $field, $check, $options, $enumString) {
        DB::statement(sprintf('ALTER TABLE %s DROP CONSTRAINT %s;', $table, $check));
        DB::statement(sprintf('ALTER TABLE %s ADD CONSTRAINT %s CHECK (%s::TEXT = ANY (ARRAY[%s]::TEXT[]))', $table, $check, $field, $enumString));
    });

}

Example usage:

$this->alterEnum('mytable', 'status', ['pending', 'accepted', 'canceled']);

Note that if you are dropping a constraint that is used in the table, you will need to either rename all instances to something that will be in the list, or delete all instances before you run this function