Re: Regarding varchar max length in postgres

On Mon, Oct 15, 2018 at 7:54 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Durgamahesh Manne <maheshpostgres9(at)gmail(dot)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
>

Hi sir

>>> If character varying is used without length specifier, the datatype
>>> accepts strings of any size up to maximum of 1GB as i found this info
in pgdg doc

I have not used this max length 10485760 value at varchar in table of db
as well as i have not confused about this maximium length of the string for
varchar upto 1GB

I have used this column datatype varchar with out using any limit

I have checked with more than above value by creating table test
with create table test(id serial primary key, str varchar(10485761)) as an
example

ERROR: length for type varchar cannot exceed 10485760

text variable unlimited length
character varying(*n*), varchar(*n*) variable-length with limit
as per the documented text is with unlimited length and varchar variable
length is with limit 1GB

So i need unlimited length data type for required column of the table for
storing the large values

is there any issue to use unlimited length datatype text for the required
column of the table instead of using varchar ?

Regards

Durgamahesh Manne