Lets say I have an existing database and I want to add a column that has a uniqueness constraint and a non-null constraint. For the non-null constraint a solution would be something like this add :newcolumn, :string, default: "defaultvalue", null: false. But my migration would still not work because of my uniqueness constraint. I was thinking something like default: fragment(now()) but my column has the string datatype. Also, there is the problem that the default is called at compile-time and you might end up with the same value in the records and violating the unique constraint. What is the best way I can solve it?
1 Like
Generally the solution is adding the column as nullable, then backfill the contents by whatever means you have to do so, and only in a seconds step make it not nullable. This becomes even more important if you have multiple nodes and rolling deployments, as nodes with the old version might run at the same time as nodes with the new version. There you can only add the “not null” after all nodes have been updated, but the column needs to exist before the first one updates.
This makes sense for the not-null constraint but how would I handle the uniqueness constraint. Is it possible to backfill in my case unique random strings in the migration for existing records?
You might be missing the fact that in postgres (and sql) NULL != NULL. Therefore you can have an unique constraint on an column with multiple rows having no value a.k.a. NULL.
Ok this makes sense now thanks.
