Thread: Regarding varchar max length in postgres

On 10/5/18 8:18 AM, Durgamahesh Manne wrote:
> Hi
> 
> please let me know the max length of varchar  & text in postgres

https://www.postgresql.org/docs/10/static/datatype-character.html
> 
> 
> 
> Regards
> 
> Durgamahesh Manne


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Durgamahesh Manne schrieb am 15.10.2018 um 11:05:
>         On 10/5/18 8:18 AM, Durgamahesh Manne wrote:
>         > Hi
>         >
>         > please let me know the max length of varchar  & text in postgres
> 
>         https://www.postgresql.org/docs/10/static/datatype-character.html
> 
>      Hi 
> 
>     Thank you for this information
> 
>     as per the postgresql documentation   please ignore incomplete message i mailed to respected community members 
> 
>      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
> 
> 
>    create table test(id serial primary key, str varchar(10485761));
> 
>      ERROR: length for type varchar cannot exceed 10485760 

Further down on that page the overall limit is documented: 

    In any case, the longest possible character string that can be stored is about 1 GB

So the part that you quoted implicitly means "accepts strings of any size  - up to the maximum of 1GB"
Maybe it makes sense to make that clearer at that point.

Regards
Thomas

Durgamahesh Manne wrote:
> was there any specific reason that you have given max length for varchar is limited to 10485760 value?
> 
> why you have not given max length for varchar is unlimited like text datatype ?
> 
> character varying(n), varchar(n)variable-length with limit 
> character(n), char(n)fixed-length, blank padded
> textvariable unlimited length

The data type "text" has the same size limit of 1GB.
"character varying" (without type modifier) and "text" are pretty much
identical.

Since data of these types are loaded into memory when you read them
from or write them to the database, you usually start having problems
long before you reach that limit.

If you want to store huge text files, either store them outside the
database or use Large Objects, which can be read and written in chunks.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Durgamahesh Manne schrieb am 15.10.2018 um 11:18:
> was there any specific reason that you have given max length for varchar is limited to 10485760 value?
> 
> why you have not given max length for varchar is unlimited like text datatype ?
> 
> |character varying(/|n|/)|, |varchar(/|n|/)|variable-length with limit 
> |character(/|n|/)|, |char(/|n|/)|fixed-length, blank padded
> |text|variable unlimited length

It "text" type is a "character string" just like all the other character types and thus is also limited to 1GB

"text", "varchar", "character varying" and "character" are all identical in how they are stored and processed. 

Thomas

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

On 10/15/18 8:56 AM, Durgamahesh Manne wrote:
> 
> 
> On Mon, Oct 15, 2018 at 9:07 PM David G. Johnston 
> <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
> 
>     On Mon, Oct 15, 2018 at 8:24 AM Durgamahesh Manne
>     <maheshpostgres9@gmail.com <mailto:maheshpostgres9@gmail.com>> wrote:
> 
>         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 ?
> 
> 
>     Between the two you should use the "text" data type for those
>     columns.  You will need to describe your use case in more detail if
>     you want input as to whether you need to use the large object API
>     instead.
> 
>     The database cannot store an unlimited amount of data in a single
>     row+column (cell) - you will still encounter a physical limit to the
>     number of bytes able to be stored in a single cell when using text.
> 
>     David J.
> 
> 
> 
> Hi sir
> 
>   i need to store more values on required column of the table by using 
> text  than varchar
> 
> you said that physical limit needs to be stored values  in column of the 
> table
> 
> |text|    variable unlimited length

There are two limits at work here. n in varchar(n) and char(n) is the 
number of characters that can be stored. Text does not have the ability 
to limit the characters on declaration. In the special case of varchar 
(no n declared) it is equal to text. All the character types(varchar, 
char, text) have a storage limit of 1GB per field. This storage value is 
not necessarily a one to one relationship to n as the encoding for the 
string maybe a multibyte one and a character may occupy multiple bytes.





> 
> 
> 
> ype
> Thank you for this valuable information
> 
>   PostgreSQL is always no 1 world s leading open source  RDBMS
> 
> 
> 
>   I request you all community members to provide built in bdr v3 version 
> replication for public as multimaster replication is on high priority  
> against other dbms


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 10/15/18 8:56 AM, Durgamahesh Manne wrote:


>   I request you all community members to provide built in bdr v3 version 
> replication for public as multimaster replication is on high priority  
> against other dbms

BDR v3 is third party extension from 2ndQuadrant, it is not community 
code. Elements of it have found there into the community code as logical 
replication, but that is as far as it has gotten. You will need to take 
this up with 2ndQuadrant.



-- 
Adrian Klaver
adrian.klaver@aklaver.com