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 ?

score 11 · Accepted Answer · answered Jun 17 '15 at 07:18

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!

Exactly what I was looking for. Just to remove the constraint and make it a VARCHAR. enums are not fun to work. Just use VARCHAR and let your code do the validation. — ultrasamad, Sep 06 '20 at 16:36

score 4 · Answer 2 · answered Mar 24 '16 at 10:57

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

Update enum column in Laravel migration using PostgreSQL

2 Answers2

Linked