CODEWITHSUNDEEP
laravelpostgresql
BigJobbies
BigJobbies

Reputation: 4045

Error with index when converting Laravel mysql migrations to postgresql

I have written about 10 Laravel migrations for a MySQL database. I want to now switch out my database for a Postgresql database but am having some trouble with indexes it seems.

I followed a tutorial about a voting module so I didnt write the migrations myself but they did all work when migrating on MySQL.

The error im getting is as follows;

SQLSTATE[42P07]: Duplicate table: 7 ERROR:  relation "poll_id" already exists (SQL: create index "poll_id" on "gp_poll_votes" ("poll_id"))

There is no duplicate table and these migrations have been working for the last year or so.

The migration it is getting stuck on is as follows;

Schema::create('gp_poll_votes', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->bigInteger('poll_id')->unsigned();
    $table->bigInteger('poll_option_id')->unsigned();
    $table->bigInteger('vote_count');
    $table->timestamps();

    $table->index('poll_id', 'poll_id');
    $table->index('poll_option_id', 'poll_option_id');
    $table->foreign('poll_id')->references('id')->on('gp_polls')->onDelete('cascade');
    $table->foreign('poll_option_id')->references('id')->on('gp_poll_options')->onDelete('cascade');
});

There is 2 more migrations associated with the votes which are run before the erroring one, which are;

Schema::create('gp_polls', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->tinyInteger('status')->nullable(false)->default(1);
    $table->timestamps();
});

and 

Schema::create('gp_poll_options', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->bigInteger('poll_id')->unsigned();
    $table->bigInteger('image_id')->unsigned();
    $table->tinyInteger('status')->nullable(false)->default(1);
    $table->timestamps();

    $table->index('poll_id', 'poll_id');
    $table->index('image_id', 'image_id');
    $table->foreign('image_id')->references('id')->on('gp_submit_images');
    $table->foreign('poll_id')->references('id')->on('gp_polls')->onDelete('cascade');
});

Upvotes: 0

Views: 587

Answers (1)

lagbox
lagbox

Reputation: 50491

In Postgres the index names have to be unique across the database it would seem, in MySQL that doesn't seem to matter.

You can create your indexes without passing a second argument, the name of the index, and Blueprint will create a more unique index name for you.

$table->index('poll_id'); // "gp_poll_options_poll_id_index"

It will use something like "{$prefix}{$table}_{$column}_{$typeOfIndex}" to generate the index name for you.

Upvotes: 1

Related Questions