[FIXED] Postgres and Laravel how to change column from type string to integer?

Issue

I am trying to change a column from type string to integer on Postgres and Laravel 6.x. I've tried to do this with a migration like so:

    public function up()
    {
        Schema::table('job_listings', function (Blueprint $table) {
            $table->integer('company_id')->change();
        });
    }

When I run this migration I get an error that the column cannot be automatically cast to an integer:

In Connection.php line 664:

  SQLSTATE[42804]: Datatype mismatch: 7 ERROR:  column "company_id" cannot be cast automatically to type integer
  HINT:  You might need to specify "USING company_id::integer". (SQL: ALTER TABLE job_listings ALTER company_id TYPE INT)


In PDOStatement.php line 123:

  SQLSTATE[42804]: Datatype mismatch: 7 ERROR:  column "company_id" cannot be cast automatically to type integer
  HINT:  You might need to specify "USING company_id::integer".


In PDOStatement.php line 121:

  SQLSTATE[42804]: Datatype mismatch: 7 ERROR:  column "company_id" cannot be cast automatically to type integer
  HINT:  You might need to specify "USING company_id::integer".

How do we specify USING to change this column from type string to integer in PostgreSQL?

Solution

You must specify an explicit cast since there is no implicit (automatic) cast from text or varchar to integer. I don't know of a Laravel function to specify the cast so I would suggest you use raw DB statement to achieve this.

You could do this:

public function up()
{
    DB::statement('ALTER TABLE job_listings ALTER COLUMN 
                  company_id TYPE integer USING (company_id)::integer');
}

There can also be cases where there are whitespace in your text or varchar fields so you would have to trim before casting

public function up()
{
    DB::statement('ALTER TABLE job_listings ALTER COLUMN 
                  company_id TYPE integer USING (trim(company_id))::integer');
}

Answered By - Clement Sam