Laravel & PostGres - Migration down() will not drop table (TimeScaleDB Extension)

Question

Here is what create_facts_table looks like:

class CreateFactsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::connection('pgsql')->create('facts', function (Blueprint $table) {
            $table->bigInteger('entry_id');
            $table->string('tag');
            $table->timestampTz('time');
            $table->double('sensor_value');

            $table->index(['entry_id', 'tag', 'time']);
            $table->unique(['entry_id', 'tag', 'time']);
        });

        DB::connection('pgsql')->statement('SELECT create_hypertable(\'facts\', \'time\');');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        // Tried both, neither would delete the table
        Schema::connection('pgsql')->dropIfExists('facts');
        //DB::connection('pgsql')->statement('DROP TABLE facts;');
    }
}

I don't get any error from down(). I am able to login as the DB user specified in my .env file and run DROP TABLE facts.

When I run php artisan migration:fresh up() fails to create the table and throws a duplicate table error:

Duplicate table: 7 ERROR:  relation "facts" already exists

After manually deleting the table, I can then run php artisan migration:fresh. I must specify connection('pgsql') as I'm using multiple databases. Semi-unrelated, but I'm using TimeScaleDB extension (hence create_hypertable())

score 1 · Accepted Answer · answered Oct 07 '21 at 13:00

I dug a bit deeper on this recently. I hadn't directly mentioned I was using the TimeScaleDB extension ('SELECT create_hypertable(\'facts\', \'time\');').

I updated the title of the question incase someone lands here from Google in the future.

Here is what I have learned: When php artisan migration:fresh is run, it attempts to drop all tables in batch and does not use the down() method. TimeScaleDB hypertables cannot be deleted in batch.

The solution is to use php artisan migration:refresh instead which will run the defined drop() operations for each table.

Source: https://stackoverflow.com/a/69105447/5449796

Laravel & PostGres - Migration down() will not drop table (TimeScaleDB Extension)

1 Answers1