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

Question

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?

Answer 1

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');
}