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