[ruby-on-rails] Difference between string and text in rails?

I'm making a new web app using Rails, and was wondering, what's the difference between string and text? And when should each be used?

If you are using postgres use text wherever you can, unless you have a size constraint since there is no performance penalty for text vs varchar

There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead

PostsgreSQL manual

If the attribute is matching f.text_field in form use string, if it is matching f.text_area use text.

Use string for shorter field, like names, address, phone, company

Use Text for larger content, comments, content, paragraphs.

My general rule, if it's something that is more than one line, I typically go for text, if it's a short 2-6 words, I go for string.

The official rule is 255 for a string. So, if your string is more than 255 characters, go for text.

String if the size is fixed and small and text if it is variable and big. This is kind of important because text is way bigger than strings. It contains a lot more kilobytes.

So for small fields always use string(varchar). Fields like. first_name, login, email, subject (of a article or post) and example of texts: content/body of a post or article. fields for paragraphs etc

String size 1 to 255 (default = 255)

Text size 1 to 4294967296 (default = 65536)2

String translates to "Varchar" in your database, while text translates to "text". A varchar can contain far less items, a text can be of (almost) any length.

For an in-depth analysis with good references check http://www.pythian.com/news/7129/text-vs-varchar/

Edit: Some database engines can load varchar in one go, but store text (and blob) outside of the table. A SELECT name, amount FROM products could, be a lot slower when using text for name than when you use varchar. And since Rails, by default loads records with SELECT * FROM... your text-columns will be loaded. This will probably never be a real problem in your or my app, though (Premature optimization is ...). But knowing that text is not always "free" is good to know.

As explained above not just the db datatype it will also affect the view that will be generated if you are scaffolding. string will generate a text_field text will generate a text_area

The accepted answer is awesome, it properly explains the difference between string vs text (mostly the limit size in the database, but there are a few other gotchas), but I wanted to point out a small issue that got me through it as that answer didn't completely do it for me.

The max size :limit => 1 to 4294967296 didn't work exactly as put, I needed to go -1 from that max size. I'm storing large JSON blobs and they might be crazy huge sometimes.

Here's my migration with the larger value in place with the value MySQL doesn't complain about.

Note the 5 at the end of the limit instead of 6

class ChangeUserSyncRecordDetailsToText < ActiveRecord::Migration[5.1]
  def up
    change_column :user_sync_records, :details, :text, :limit => 4294967295
  end

  def down
    change_column :user_sync_records, :details, :string, :limit => 1000
  end
end

If you are using oracle... STRING will be created as VARCHAR(255) column and TEXT, as a CLOB.

NATIVE_DATABASE_TYPES = {
    primary_key: "NUMBER(38) NOT NULL PRIMARY KEY",
    string: { name: "VARCHAR2", limit: 255 },
    text: { name: "CLOB" },
    ntext: { name: "NCLOB" },
    integer: { name: "NUMBER", limit: 38 },
    float: { name: "BINARY_FLOAT" },
    decimal: { name: "DECIMAL" },
    datetime: { name: "TIMESTAMP" },
    timestamp: { name: "TIMESTAMP" },
    timestamptz: { name: "TIMESTAMP WITH TIME ZONE" },
    timestampltz: { name: "TIMESTAMP WITH LOCAL TIME ZONE" },
    time: { name: "TIMESTAMP" },
    date: { name: "DATE" },
    binary: { name: "BLOB" },
    boolean: { name: "NUMBER", limit: 1 },
    raw: { name: "RAW", limit: 2000 },
    bigint: { name: "NUMBER", limit: 19 }
}

https://github.com/rsim/oracle-enhanced/blob/master/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb