How to migrate complex Rails database to use UUID primary keys Postgresql

Question

I have a database I would like to convert to use UUID's as the primary key in postgresql.

I have roughly 30 tables with deep multi-level associations. Is there an 'easy' way to convert all current ID's to UUID?

From this: https://coderwall.com/p/n_0awq, I can see that I could alter the table in migration. I was thinking something like this:

for client in Client.all
  # Retrieve children
  underwritings = client.underwritings
  # Change primary key
  execute 'ALTER TABLE clients ALTER COLUMN id TYPE uuid;'
  execute 'ALTER TABLE clients ALTER COLUMN id SET DEFAULT uuid_generate_v1();'
  # Get new id - is this already generated?
  client_id = client.id
  for underwriting in underwritings
    locations = underwriting.locations
    other_record = underwriting.other_records...

    execute 'ALTER TABLE underwritings ALTER COLUMN id TYPE uuid;'
    execute 'ALTER TABLE underwritings ALTER COLUMN id SET DEFAULT uuid_generate_v1();'
    underwriting.client_id = client_id
    underwriting.saved
    underwriting_id = underwriting.id

    for location in locations
      buildings = location.buildings
      execute 'ALTER TABLE locations ALTER COLUMN id TYPE uuid;'
      execute 'ALTER TABLE locations ALTER COLUMN id SET DEFAULT uuid_generate_v1();'
      location.undewriting_id = underwriting_id
      location.save
      location_id = location.id

      for building in buildings
      ...
      end
    end
    for other_record in other_records
      ...
    end
    ... 
    ...
  end
end

Questions:

• Will this work?
• Is there an easier way to do this?
• Will child records be retrieved properly as long as they are retrieved before the primary key is changed?
• Will the new primary key be already generated as soon as the alter table is called?

Thanks very much for any help or tips in doing this.

Answer 1

Didn't want to add foreign keys, and wanted to to use a rails migration. Anyways, here is what I did if others are looking to do this (example for 2 tables, I did 32 total):

  def change
    execute 'CREATE EXTENSION "uuid-ossp";'
    execute <<-SQL
      ALTER TABLE buildings ADD COLUMN guid uuid DEFAULT uuid_generate_v1() NOT NULL;
      ALTER TABLE buildings ALTER COLUMN guid SET DEFAULT uuid_generate_v1();
      ALTER TABLE buildings ADD COLUMN location_guid uuid;

      ALTER TABLE clients ADD COLUMN guid uuid DEFAULT uuid_generate_v1() NOT NULL;
      ALTER TABLE clients ALTER COLUMN guid SET DEFAULT uuid_generate_v1();
      ALTER TABLE clients ADD COLUMN agency_guid uuid;
      ALTER TABLE clients ADD COLUMN account_executive_guid uuid;
      ALTER TABLE clients ADD COLUMN account_representative_guid uuid;
    SQL

    for record in Building.all
      location = record.location
      record.location_guid = location.guid

      record.save
    end

    for record in Client.all
      agency = record.agency
      record.agency_guid = agency.guid

      account_executive = record.account_executive
      record.account_executive_guid = account_executive.guid unless account_executive.blank?

      account_representative = record.account_representative
      record.account_representative_guid = account_representative.guid unless account_representative.blank?

      record.save
    end

    execute <<-SQL
      ALTER TABLE buildings DROP CONSTRAINT buildings_pkey;
      ALTER TABLE buildings DROP COLUMN id;
      ALTER TABLE buildings RENAME COLUMN guid TO id;
      ALTER TABLE buildings ADD PRIMARY KEY (id);
      ALTER TABLE buildings DROP COLUMN location_id;
      ALTER TABLE buildings RENAME COLUMN location_guid TO location_id;

      ALTER TABLE clients DROP CONSTRAINT clients_pkey;
      ALTER TABLE clients DROP COLUMN id;
      ALTER TABLE clients RENAME COLUMN guid TO id;
      ALTER TABLE clients ADD PRIMARY KEY (id);
      ALTER TABLE clients DROP COLUMN agency_id;
      ALTER TABLE clients RENAME COLUMN agency_guid TO agency_id;
      ALTER TABLE clients DROP COLUMN account_executive_id;
      ALTER TABLE clients RENAME COLUMN account_executive_guid TO account_executive_id;
      ALTER TABLE clients DROP COLUMN account_representative_id;
      ALTER TABLE clients RENAME COLUMN account_representative_guid TO account_representative_id;
    SQL
  end