Changing a column type to longer strings in rails

Question

At the first migration, I declared on a column content to be string Activerecord made it to be string(255) according to annotate gem.

After I push the app to heroku, which uses postgres, if I enter in the form in content a string longer than 255 I get the error

PGError: ERROR: value too long for type character varying(255)

Problem is I need that content to contain a string that is extremely long perhaps (free text, could be thousands of chars)

1. What variable (is string is not appropriate for this) would pg accept?
2. How do I create a migration to replace the type of that column

thanks

Answer 1

You should use text with Rails if you want a string with no length limit. A migration like this:

def up
  change_column :your_table, :your_column, :text
end
def down
  # This might cause trouble if you have strings longer
  # than 255 characters.
  change_column :your_table, :your_column, :string
end

should sort things out. You might want :null => false or some other options on the end of that too.

When you use a string column without an explicit limit, Rails will add an implicit :limit => 255. But if you use text, you'll get whatever arbitrary length string type the database supports. PostgreSQL allows you to use a varchar column without a length but most databases use a separate type for that and Rails doesn't know about varchar without a length. You have to use text in Rails to get a text column in PostgreSQL. There's no difference in PostgreSQL between a column of type text and one of type varchar (but varchar(n) is different). Furthermore, if you're deploying on top of PostgreSQL, there's no reason to use :string (AKA varchar) at all, the database treats text and varchar(n) the same internally except for the extra length constraints for varchar(n); you should only use varchar(n) (AKA :string) if you have an external constraint (such as a government form that says that field 432 on form 897/B will be 23 characters long) on the column size.

As an aside, if you are using a string column anywhere, you should always specify the :limit as a reminder to yourself that there is a limit and you should have a validation in the model to ensure that the limit is not exceeded. If you exceed the limit, PostgreSQL will complain and raise an exception, MySQL will quietly truncate the string or complain (depending on the server configuration), SQLite will let it pass as-is, and other databases will do something else (probably complain).

Also, you should also be developing, testing, and deploying on top of the same database (which will usually be PostgreSQL at Heroku), you should even use the same versions of the database server. There are other differences between databases (such as the behavior of GROUP BY) that ActiveRecord won't insulate you from. You might be doing this already but I thought I'd mention it anyway.

---

Update: Newer versions of ActiveRecord do understand varchar without a limit so, with PostgreSQL at least, you can say:

change_column :your_table, :your_column, :string, limit: nil

to change a varchar(n) column to varchar. text and varchar are still the same thing as far as PostgreSQL is concerned but some form builders will treat them differently: varchar gets an <input type="text"> whereas text gets a multi-line <textarea>.

Answer 2

While the accepted answer is excellent, I wanted to add an answer here that hopefully better deals with the original posters question part 2, for non experts like myself.

2. How do I create a migration to replace the type of that column

generating scaffold migration

You can generate a migration to hold your change by typing in your console (just replace the table for your tables name, and column for you column name)

rails generate migration change_table_column

This will generate skeleton migration inside you Rails application /db/migrate/ folder. This migration is a placeholder for your migration code.

For example I want to create a migration to change the type of a column from string to text, in a table called TodoItems:

class ChangeTodoItemsDescription < ActiveRecord::Migration
  def change
     # enter code here
     change_column :todo_items, :description, :text
  end
end

Running your migration

Once you've entered the code to change the column just run:

rake db:migrate

To apply your migration. If you make an error you can always revert the change with:

rake db:rollack

Up and Down methods

The accepted answer references Up and Down methods, instead of the newer Change method. Since rails 3.2 old style Up and Down Methods presented a few advantages over the newer Change method. 'Up and Down' avoid ActiveRecord::IrreversibleMigration exception. Since the release of Rails 4 you can use reversible to avoid this error:

class ChangeProductsPrice < ActiveRecord::Migration
  def change
    reversible do |dir|
      change_table :products do |t|
        dir.up   { t.change :price, :string }
        dir.down { t.change :price, :integer }
      end
    end
  end
end

Enjoy Rails :)