Posted about 1 month ago. Visible to the public. Linked content.

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

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

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

By refactoring problematic code and creating automated tests, makandra can vastly improve the maintainability of your Rails application.

Owner of this card:

Avatar
Michael Leimstädtner
Last edit:
26 days ago
by Michael Leimstädtner
Keywords:
string, text
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Michael Leimstädtner to makandra dev
This website uses short-lived cookies to improve usability.
Accept or learn more