Home / server / Questions / 240813
Accepted
Eonil
Asked: 2011-02-28 03:07:37 +0800 CST

What's difference between "varchar" and "text" type in PostgreSQL?

  • 772

All I know about differences of them is varchar has limit, and text is not. The documentation does not mention about this.

Is that really the only difference? No consideration about performance or etc?

database postgresql

3 Answers

  1. Best Answer

    The background of this is: The old Postgres system used the PostQUEL language and used a data type named text (because someone thought that was a good name for a type that stores text). Then, Postgres was converted to use SQL as its language. To achieve SQL compatibility, instead of renaming the text type, a new type varchar was added. But both type use the same C routines internally.

    Now, to some degree and in some places, text is hardcoded as a default type, in case nothing else can be derived. Also, most functions are only available as taking a text argument or returning text. The two types are binary compatible, so casting is a trivial parse-time operation. But using text is still overall more natural to the system.

    But aside from these fine points, there is no noticeable difference. Use whichever one looks prettier to you. ;-)

    • 25

  2. See this similar question. The jist is that there is no difference, but specifying a maximum length such as varchar(n) is generally not in your favor, as it uses more space but doesn't improve performance.

    • 1

  3. http://www.postgresql.org/docs/8.4/interactive/datatype-character.html

    character varying(n), varchar(n)        variable-length with limit
text                                    variable unlimited length

    text without declared length. There are no performance differences between these two types.

    • 0