On Mon, Oct 15, 2018 at 7:54 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Durgamahesh Manne <maheshpostgres9@xxxxxxxxx> 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
>>> 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(, varchar( | 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
