PostgreSQL: Difference between text and varchar columns
PostgreSQL offers three character types for your columns:
character varying(n)(also called
string): Contents are limited to n characters, smaller contents are allowed.
character(n): All contents are padded with spaces to allocate exactly n characters.
text: There is no upper or lower character limit (except for the absolute maximum of 1 GB).
The documentation states:
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.
Up to Rails 3, the column type
string defaulted to creating a varchar column limited to 255 characters. Many applications therefore have tables such as those in their
create_table :my_table do |t| t.string :my_string, limit: 255 end
If you don't add respective validations to your model, sooner or later you will see an exception like this when trying to save user provided data:
ActiveRecord::ValueTooLong: PG::StringDataRightTruncation: ERROR: value too long for type character varying(255)
Since Rails 4+
string columns no longer imply a limit of 255 characters and are therefore handled just like
If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension.
Please note that while both database columns are handled uniformly, libraries like simple_form will represent them differently in your views. The named gem renders a
<textarea> for all columns of the type
PostgreSQL handles Rails 4+
string columns indifferently. That said, there is still a semantic difference that might be used by your library's code.