Difference between string and text in rails?

Question

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?

Acarology

Answer 1

The difference relies in how the symbol is converted into its respective column type in query language.

with MySQL :string is mapped to VARCHAR(255)

• https://edgeguides.rubyonrails.org/active_record_migrations.html

:string |                   VARCHAR                | :limit => 1 to 255 (default = 255)  
:text   | TINYTEXT, TEXT, MEDIUMTEXT, or LONGTEXT2 | :limit => 1 to 4294967296 (default = 65536)

Reference:

https://hub.packtpub.com/working-rails-activerecord-migrations-models-scaffolding-and-database-completion/

When should each be used?

As a general rule of thumb, use :string for short text input (username, email, password, titles, etc.) and use :text for longer expected input such as descriptions, comment content, etc.

Girandole

I think a better rule of thumb is to always use :text. See depesz.com/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text – Cue

For MySQL - not so much, you can have indexes on varchars, you cannot on text. – Frazil

PostgreSQL implementation prefers text. The only difference for pg string/text is constraint on length for string. No performance differences. – Ostler

This doesn't seem to be the whole story with ActiveRecord. Saving the value true to a varchar (ergo, string type field) in MySQL serializes the value to 1 (which is completely fair). However, under text type, storing the value "true" ends up serializing it as a singular char t. I migrated a column without realizing this and all future rows where the value is true is now t. Does anyone have any insights into this behaviour? – Marcelline

So what does that mean @OmarQureshi? Is it that "where" clauses will be much slower on text columns than string columns? – Bombacaceous

@elli0t it means that you wont be able to index. If this is important, then you should not use text on MySQL – Frazil

Answer 2

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

Frazil

But in the interest of being database agnostic, is this the best approach? What if you want to change the database? I grant, in the real world that doesn't happen that often, but still...if there's 'no peformance difference' why not stick to the expected use of string for short things and text for longer things? And given your own comment indexing strings, still seems the best approach. – Fanfaron

There's any number of reasons why it might become necessary in the Real World, where it's best to shed the notion that there is One True Solution to any problem. – Fanfaron

That may be so, but database agnosticism is a false prophet. – Frazil

Does anyone have any information about whether the performance penalty is significant or is this a case of premature optimization? My guess is you won't ever notice a difference, which the opening of the paragraph seems to confirm: "There is no performance difference among these three types". – Serialize

If there is no performance penalty, why not use the one which gives you the most flexibility to store all the data you can? also depesz.com/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text – Frazil

You make a good point, but I'm not entirely convinced. The arguments in that blog post for using text over (n) data types are convincing, but the argument for using text over varchar is not. He says they're the same but prefers text because varchar can be confused with varchar(n) and because text is less characters to type. But using text instead of varchar, you lose the context that the data stored should not be long. For example, storing a username with text seems misleading to me. – Serialize

So, the reason I personally use text over varchar is that I don't think that for certain types of data, I should put arbitrary restrictions on the kind of data that I want to store. I tend to only use varchar whenever I have a fixed size restriction and everything else is text, that way I don't have to think about it any more. – Frazil

In the case of database agnosticism, what if you have already lost it for other reasons like storing arrays or hstores or tsvectors? Can I use text for all text fields now? – Lareine

Not only can you, but, you probably should – Frazil

Answer 3

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.

Dovetail

Answer 4

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

Goodsell

Answer 5

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

Unwritten

Answer 6

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.

Atharvaveda

Answer 7

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

Zante

For those who use postgresql and would like to store JSON data it's better to use native jsonb type (but check your postgresql version first). – Deputy

Answer 8

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

Pacorro

Answer 9

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

Pentastyle