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>.