Efcore.pg: Migrating string column to int column fails

[fernandolguevara]

Npgsql.PostgresException (0x80004005): 42804: column "column_name" cannot be cast automatically to type integer

[ykirkanahtar]

same here

Failed executing DbCommand (86ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE SEQUENCE "AspNetUsers_Id_seq" START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE NO CYCLE;
ALTER TABLE "AspNetUsers" ALTER COLUMN "Id" TYPE int4;
ALTER TABLE "AspNetUsers" ALTER COLUMN "Id" SET NOT NULL;
ALTER TABLE "AspNetUsers" ALTER COLUMN "Id" SET DEFAULT (nextval('"AspNetUsers_Id_seq"'));
ALTER SEQUENCE "AspNetUsers_Id_seq" OWNED BY "AspNetUsers"."Id"

42804: column "Id" cannot be cast automatically to type integer

[roji]

Neither of you specified the column type before the attempted change to int4, but this is very probably a duplicate of #144.

To summarize, if you're trying to convert a column from, say, text to int4, PostgreSQL will refuse because some already-existing values cannot be converted (anything non-numeric). You can still do the conversion by adding a USING clause to an ALTER TABLE ... ALTER COLUMN statement, specifying exactly how you'd like the conversion to happen and taking responsibility in case the conversion cannot work smoothly.

The Npgsql EF Core provider simply follows this logic. If your conversion always works (e.g. int4 -> text), then the generated migration will silently work, but if not, you must manually write the ALTER TABLE statement in the migration using raw SQL.

[huxaiphaer]

@roji would mind giving an example of migration with a manual raw SQL.

Thanks.