Re: Regarding varchar max length in postgres - Mailing list pgsql-general

Durgamahesh Manne <maheshpostgres9@gmail.com> writes:
>>> If character varying is used without length specifier, the type
>>> accepts strings of any size
>>> but varchar does not accept more than this 10485760 value

You're confusing the size of string that can be stored with the
largest value accepted for "n" in "varchar(n)".  This is documented,
in the same place that people have been pointing you to:

    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.)

As you found out, the limit for "n" is ~ 10 million.

In principle, we could have allowed it to be as much as 1Gb divided by
the maximum character length of the database's encoding, but it did
not seem like a great idea for the limit to be encoding-dependent.

As the last sentence in the doc paragraph points out, the preferred
thing to do if you just want to allow very long strings is to leave
off "(n)" altogether.

The subtext here, which maybe we ought to state in a more in-your-face
way, is that if you use char(N) or varchar(N) without a concrete
application-driven reason why N has to be that particular value,
no more or less, then You're Doing It Wrong.  Artificially-chosen
column width limits are a bad idea left over from the days of
punched cards.  The reason the limit on N is much smaller than it
could theoretically be is that column declarations with very large
N are, without exception, violations of this principle.

                        regards, tom lane