Ecto migration with default value from another column in same table

Probably it will be best to add the column with a default value of gen_random_uuid() and then that way they are actually UUIDs.

Thanks for your help. I ended up with this which works as intended:

  add :uuid, :string, null: false, default: fragment("gen_random_uuid()")

I would strongly suggest using the binary_id type which will much more compactly store the underlying UUID.

Will do, does this just reduce the overall size of the DB?

Smaller size is the first order impact (the string representation is twice as big as the binary representation) but at large scale the smaller size directly translates into improved query performance because the indices are smaller and thus faster to traverse.

OK that sounds like a pretty tangible benefit, have changed, thanks again.

@MarkMekhaiel If it is a table with some records (a lot of them), my recommendation is to do it with more than 1 migration to avoid having your table locked.

My suggestion is:

1. Create a new UUID column that does not have a default value and can be null.
2. Run a script to generate value for empty records in a batch of some records until all of them are updated.
3. In the same migration (using the same transaction, yes Postgres uses transactions for DDL) run your script again and change the UUID column to be not null and having gen_random_uuid() as a default value.

That way you can avoid changes in a table with a lot of records and lock the table during the generation of those values.

Thanks for the advice, I ended up copy and pasting from Backfilling Data · The Phoenix Files - batching every 2500 and throttling inbetween with a temp table to store the ID’s of completed records

Hi @benwilson512 @marciotrindade, could you suggest a book on designing(data modelling) and working with databases(especially Postgres)? I would like to get better at dealing with DB’s in an efficient way.

Thank you both of you & have a great day!