Backfilling the new_id column so it matches id for all rows in the table. ![]() Now the next part is shaping up the new field to be functionally identical to the primary key. Ruby On Rails notesĪdding the column will work with a simple add_column(: table, :new_id, :bigint, default: 0, null: false) but there is currently no helper function for creating functions and triggers so you’ll need to execute raw SQL. In order to ensure we automatically get id mirrored to new_id for all new records we will use database triggers: CREATE OR REPLACE FUNCTION mirror_table_id_to_new_id()ĮXECUTE PROCEDURE mirror_table_id_to_new_id() Īt this point you can be certain that PostgreSQL will ensure that all new rows that are inserted will have an identical value in both id and new_id fields. Later, when we backfill all values, we can get rid of the default value: ALTER TABLE "table" ADD "new_id" bigint DEFAULT 0 NOT NULL In addition it means that NOT NULL can be added at the metadata level only making the whole thing a very fast change regardless of table size. Since PostgreSQL 11, when a new field with a default value is added, it will be a fast metadata only operation. The default value is there to give us a free NOT NULL constraint. We first set up a new bigint field that is not null and has a default value of 0. However, I made the migration while working on a Ruby on Rails application so I’ve added some notes on how to deal with Rails specific issues along the way.įor reference, this migration was performed on PostgreSQL 13 and Rails 6.1. The bulk of the content is in pure SQL so that it is useful no matter what language and framework you are using since the technique is more about PostgreSQL than anything else.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |