-4

I have a table (People) with a full name column, but want to split it into a first name (and initial if it exists) column and last name column.

It's a PostgreSQL database FWIW. I'm at the point of creating the columns, so it can all be done in the migration if that's possible. The names all fit the old standard American style, that is, no compound names or hyphens. No periods after the initials and some of the first names are initials only. This will be a one shot change, and then any new entries will be First and Last names in their separate fields. The full name field will be dropped.

Thanks for any help

Greg
  • 2,359
  • 5
  • 22
  • 35

2 Answers2

3

You can do it like this in single migration

create two new columns

add_column :people, :first_name, :string
add_column :people, :last_name, :string

then

  full_names = People.all.map(&:full_name)

it return new array of full_name ['full_name','full_name1',..etc]

and then

full_names.each do |n| People.create(first_name:n.split('_')[0],
                                     last_name:n.split('_')[1])
end 

and remove full_name column

remove_column :people, :full_name 

Clumsy solution but should works

xxx
  • 506
  • 7
  • 16
  • Thank you. Had to change to `names = Person.all.map(&:name)`. Name is what my full_names are. But then I ended up with a new column first_name filled with the full_names and worst, this created a new set of entries. So to some extent they are duplicated, but the associated fields are not there. So I see two problems, parsing isn't working and a whole new set of entries is created. But this may get me started to find a solution. Easier for me than trying to do SQL. But first I have to drop the new items. – Greg Jan 06 '16 at 18:16
  • `rails generate migration RemoveLastNameFromPeople last_name:string given_name:string` got rid of the columns but not the added entries. – Greg Jan 06 '16 at 21:38
0

Make sure you don't conflate your table schema (which columns your data has) with the actual data (the rows with those columns).

Does it make sense now why removing the last_name column will not delete any rows. It will keep all rows, but delete that column from all rows.

So, as @xxx said, add the columns (migrate the schema), and then populate them (migrate the data).

If you want to clean up all those extra records created from your botched migration, well shame on you, always back up your database before running a migration. ;)

But maybe a more helpful answer: You could use the created_at date to find all the records created as part of your first migration, then delete them. Something like

# e.g. if you want to delete all records created since 
# your migration which you ran 2 days ago
People.where("created_at > ?", 2.days.ago).destroy_all

Make sure to back up your db before you try that one...

And then, back to your original question, how to migrate the data from a single name field to first/last names. Once you've added the new columns, try iterating over each person like this:

People.each do |person|
  new_first_name, new_last_name = person.name.split
  person.update_attributes(
    first_name: new_first_name,
    last_name: new_last_name
  ) 
end
mkirk
  • 3,965
  • 1
  • 26
  • 37