Difference between text and varchar (character varying)

string postgresql text types varchar
Adam Matan picture Adam Matan · Jan 31, 2011 · Viewed 328.2k times · Source

What's the difference between the text data type and the character varying (varchar) data types?

According to the documentation

If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension.

and

In addition, PostgreSQL provides the text type, which stores strings of any length. Although the type text is not in the SQL standard, several other SQL database management systems have it as well.

So what's the difference?

Answer

Frank Heikens picture Frank Heikens · Jan 31, 2011

There is no difference, under the hood it's all varlena (variable length array).

Check this article from Depesz: http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/

A couple of highlights:

To sum it all up:

  • char(n) – takes too much space when dealing with values shorter than n (pads them to n), and can lead to subtle errors because of adding trailing spaces, plus it is problematic to change the limit
  • varchar(n) – it's problematic to change the limit in live environment (requires exclusive lock while altering table)
  • varchar – just like text
  • text – for me a winner – over (n) data types because it lacks their problems, and over varchar – because it has distinct name

The article does detailed testing to show that the performance of inserts and selects for all 4 data types are similar. It also takes a detailed look at alternate ways on constraining the length when needed. Function based constraints or domains provide the advantage of instant increase of the length constraint, and on the basis that decreasing a string length constraint is rare, depesz concludes that one of them is usually the best choice for a length limit.

Related questions

String literals and escape characters in postgresql

Attempting to insert an escape character into a table results in a warning. For example: create table EscapeTest (text varchar(50)); insert into EscapeTest (text) values ('This is the first part \n And this is the second'); Produces the warning: WARNING: …

Read More
PostgreSQL newline character

How to use newline character in PostgreSQL? This is an incorrect script from my experiment: select 'test line 1'||'\n'||'test line 2'; I want the sql editor display this result from my script above: test line 1 test line 2 …

Read More
Changing a column type to longer strings in rails

At the first migration, I declared on a column content to be string Activerecord made it to be string(255) according to annotate gem. After I push the app to heroku, which uses postgres, if I enter in the form in …

Read More