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?

Clement Sam · Accepted Answer · 2021-11-08T10:03:20.657

12

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

edited Nov 08 '21 at 10:03

answered Jul 21 '20 at 01:37

Clement Sam

720
7
17

Thanks! What would you put for the `down` method? – Connor Leech Jul 22 '20 at 13:51
`$table->string('company_id')->change();` should work fine with Schema – Clement Sam Jul 22 '20 at 14:58
2

The SQL strings are both missing a closing bracket at the end - the brackets within the SQL currently don't match up. – Mog0 Aug 02 '21 at 11:26

score 0 · Answer 2 · answered Jan 04 '22 at 20:53

Even the tables has row or not, it's still return that error. So if you don't want raw query and your column doesn't have value or has but not important, just drop column and create new one:

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

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

2 Answers2

Linked