PostgreSQL: Difference between text and varchar columns

PostgreSQL offers three character types for your columns:

  • character varying(n) (also called varchar or just 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 Show archive.org snapshot 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 schema.rb:

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

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 Show archive.org snapshot will represent them differently in your views. The named gem renders a <textarea> for all columns of the type text.

TL;DR

PostgreSQL handles Rails 4+ text and string columns indifferently. That said, there is still a semantic difference that might be used by your library's code.

See also

Michael Leimstädtner Over 3 years ago