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

Jerrine

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

Liederman

Great answer. One note: Rails doesn't currently support change_column with the change method (guides.rubyonrails.org/migrations.html#using-the-change-method); if memory serves, you'll create an irreversible migration if you do that. Better to do it the old-school way with up/down methods. – Referential

@BourbonJockey: It does make sense that change wouldn't be able to automatically reverse a type change and the Migrations Guide does say that "[the change method] This method is preferred for writing constructive migrations (adding columns or tables)" and change_column isn't in the list you point at so I think you're right. I fixed it to use up/down (with a caveat on the down), thanks for the heads up. – Liederman

For other readers' future reference, converting from string to text in Postgres on Heroku in this manner will NOT lose data. – Restrainer

"you should also be developing with PostgreSQL if you're deploying to Heroku". Just wanted to point out that you can also use MySQL on Heroku. – Digress

@Dennis: Perhaps "you should develop, test, and deploy using the same database" would be more accurate. The usual problem is that people use the (ridiculous) Rails default SQLite setup and things fall apart when they deploy on top of something else. PostgreSQL is still the default and most common option at Heroku, no? – Liederman

@muistooshort ah I misinterpreted; your new suggestion is much clearer. Yes, PostgreSQL is still the default and most common option at Heroku. – Digress

On a side note, Rails's assumption that fields of unspecified length should be 255 characters is weird. It is not necessary in PostgreSQL to use text just to get unlimited length; you can just use unconstrained varchar. Rails is imposing this odd limit, not PostgreSQL. – Chavez

@CraigRinger: I'm sure the excuse is portability. Rails will also let you create a decimal(m,n) column inside SQLite which considers that an alias for real. Anyway, I think you already know how I feel about how Rails interacts with databases. – Liederman

I have to create a column context varchar(80) to my table. should I do t.text :context, limit: 80 or t.string :context, limit: 80 ? How you people decide it? -- These 2 questions I was asking myself today morning.. and then found your answer and answered my questions. You are always one of my favorite answerer. Thanks @muistooshort. I should work with you 1 day, to learn such amusing concepts from you.. You are very very good. – Worry

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 :)

Discotheque