what is the maximum length of varchar(n) in postgr

Question

Hi I am using postgresql 9.2 and I want to use varchar(n) to store some long string but I don't know the maximum length of character which varchar(n) supports. and which one is better to use so could you please suggest me? thanks

Answer 1

According to the official documentation ( http://www.postgresql.org/docs/9.2/static/datatype-character.html ):

In any case, the longest possible character string that can be stored is about 1 GB. (The maximum value that will be allowed for n in the data type declaration is less than that. It wouldn't be useful to change this because with multibyte character encodings the number of characters and bytes can be quite different. If you desire to store long strings with no specific upper limit, use text or character varying without a length specifier, rather than making up an arbitrary length limit.)

Searching online reveals that the maximum value allowed varies depending on the installation and compilation options, some users report a maximum of 10485760 characters (10MiB exactly, assuming 1-byte-per-character fixed encoding).

By "the installation and compilation options" I mean that you can always build PostgreSQL from source yourself and before you compile PostgreSQL to make your own database server you can configure how it stores text to change the maximum amount you can store - but if you do this then it means you might run into trouble if you try to use your database files with a "normal", non-customized build of PostgreSQL.

Answer 2

tl;dr: 1 GB (each character (really: codepoint) may be represented by 1 or more bytes, depending on where they are on a unicode plane - assuming a UTF-8 encoded database). You should always use text datatype for arbitrary-length character data in Postgresql now.

Explanation: varchar(n) and text use the same backend storage type (varlena): a variable length byte array with a 32bit length counter. For indexing behavior text may even have some performance benefits. It is considered a best practice in Postgres to use text type for new development; varchar(n) remains for SQL standard support reasons. NB: varchar() (with empty brackets) is a Postgres-specific alias for text.

See also: http://www.postgresql.org/about/