Migrating nullable json column in EF Core to non-nullable

Question

I am trying to migrate a previously nullable json column to a non-nullable column in EF Core using a PostgreSQL 16 database. The generated MigrationBuilder fragment looks like this:

migrationBuilder.AlterColumn<string>(
   name: "preferences",
   schema: "myschema",
   table: "mytable",
   type: "json",
   nullable: false,
   defaultValue: "",
   oldClrType: typeof(string),
   oldType: "json",
   oldNullable: true);

This of course does not work since an empty json column requires at least '{}', '' is not a valid value. I changed above code to defaultValue: "{}", but the SQL generated by EF when running the migration still tries to update null values to ''. The migration still fails even after manually making sure that there are no actual null values left in the database. The error is

Failed executing DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
UPDATE myschema."mytable" SET preferences = '' WHERE preferences IS NULL;
ALTER TABLE myschema."mytable" ALTER COLUMN preferences SET NOT NULL;
ALTER TABLE myschema."mytable" ALTER COLUMN preferences SET DEFAULT '{}';
Npgsql.PostgresException (0x80004005): 22P02: invalid input syntax for type json

Is there any way i can tell EF to skip updating existing null values?

Answer 1

I guess in my case there was some sort of caching going on. Tried it again today after rebooting my machine and the issue was gone. I ended using the suggested migrationBuilder.Sql() approach to update all affected entries.