Enum -> change() failing on PostgreSQL

I was migrating one project from MySQL to PostgreSQL and one of the migration failed where I'm changing column from boolean to enum.

I know that in PostgreSQL enum is actually varchar but not sure if this issue has been documented yet somewhere?

Here is migration:

$table->enum('status', ['requested', 'accepted', 'declined'])->default('requested')->change();

And error:

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "check"
LINE 1: ...nections" alter column "status" type varchar(255) check ("st...
^ (Connection:
pgsql, SQL: alter table "statuses" alter column "status" type varchar(2
55) check ("status" in ('requested', 'accepted', 'declined')), alter column
"status" set not null, alter column "status" set default 'requested', alte
r column "status" drop identity if exists)

Braunson

11 months ago

Level 18

Yeah this is a known limitation when migrating from boolean to enum in PostgreSQL with Laravel.

The issue occurs when PostgreSQL doesn't allow you to directly change a column type from boolean to varchar with constraints in a single statement.

So a workaround here is to drop the column first, then re-added it as a new column (enum).

// Drop the column and recreate it
$table->dropColumn('status');
$table->enum('status', ['requested', 'accepted', 'declined'])->default('requested');

IF YOU NEED TO PRESERVE DATA:

// Step 1: Add the new column
$table->enum('status_temp', ['requested', 'accepted', 'declined'])->nullable();

// Step 2: Copy data (in a separate Laravel migration)
DB::statement("UPDATE statuses SET status_temp = CASE WHEN status = true THEN 'accepted' WHEN status = false THEN 'declined' ELSE 'requested' END");

// Step 3: Drop the old column and rename the temp column
$table->dropColumn('status');
$table->renameColumn('status_temp', 'status');
$table->string('status')->default('requested')->change();

Please or to participate in this conversation.