phplaravellaravel-migrations

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

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())

Solution

  • 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 in case someone lands here from Google in the future.

    Here is what I have learned:

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