Alternate title: how to lose that “you done goofed” feeling.
So let’s say you made an update to your table to support longitude and latitude, because you knew you’d need them someday but the push right now was just to add the fields so they could start getting populated.
And let’s go further and say it was item 4892 on your plate, and validating numbers is soooo hard, so you just made them strings:
def change add_column :locations, :longitude, :string, :default => '', :null => false add_column :locations, :latitude, :string, :default => '', :null => false end
…And that’s great, until you actually want to do something with those, like, oh, run calculations on them, because calculations are done with numbers and you’ve got strings, so you can either push your string agenda forward with a lot of expensive casts, or you can admit there’s a problem and you’ve got to change it.
Unfortunately, a straight-up “change_column :locations, :longitude, :float, :default => 0, :null => false” isn’t going to cut it, at least in PostgreSQL (which we’re using precisely because we’re moving to geo stuff, more on that later.) Those empty strings are going to fail on the conversion, because it’s not obvious that ” == 0, and you’ll get PG::Error: ERROR: column “latitude” cannot be cast to type double precision
So you think it’s just a matter of changing the empty strings to zeros ahead of time, like “update locations set longitude=’0′ where longitude=””? Close, but nope. PostgreSQL needs a hint, which means you’ve got to drop down to raw SQL:
execute("ALTER TABLE locations ALTER COLUMN latitude TYPE float USING (latitude::float)")
And that leads to “PG::Error: ERROR: default for column “latitude” cannot be cast to type double precision” – it doesn’t look like you can alter a column type and default in the same statement, so you’ve got to break them up.
execute("ALTER TABLE web_listings ALTER COLUMN latitude drop default") execute("ALTER TABLE locations ALTER COLUMN latitude TYPE float USING (latitude::float)") execute("ALTER TABLE locations ALTER COLUMN latitude set default 0")
And that seems to work. Now you’ve got floats in your database and can start doing geo math – more on that in the next post.