Thread: Maximum size for char or varchar with limit
Hello, Does anyone know what the maximum length is for char or varchar columns with limit. I saw some answers to this same question referring to section 8.3 of the docs, but I don't see the actual numbers there. I know you can go to 1GB if you don't specify the limit, but I would like to know what the max limit is you can use. It seems to be 10485760 in my install. Is this fixed in PostgreSQL or does it depend on some configuration setting? Does it depend on the version (I am using 8.4)? Here is my test-sql: create table test (x varchar(100000000)) Error: ERROR: length for type varchar cannot exceed 10485760 SQLState: 22023 ErrorCode: 0 Thanks in advance, Rob
On Wednesday 08 December 2010 5:47:25 am Rob Gansevles wrote: > Hello, > > Does anyone know what the maximum length is for char or varchar > columns with limit. > I saw some answers to this same question referring to section 8.3 of > the docs, but I don't see the actual numbers there. > > I know you can go to 1GB if you don't specify the limit, but I would > like to know what the max limit is you can use. > > It seems to be 10485760 in my install. > Is this fixed in PostgreSQL or does it depend on some configuration > setting? Does it depend on the version (I am using 8.4)? > > > Here is my test-sql: > > create table test (x varchar(100000000)) > > Error: ERROR: length for type varchar cannot exceed 10485760 > SQLState: 22023 > ErrorCode: 0 > > Thanks in advance, > > Rob I think you are looking for this: http://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table.2C_and_a_database.3F -- Adrian Klaver adrian.klaver@gmail.com
Adrian, Thanks for the reply, but this refers to max row or field size, it does not tell me where the max varchar limit of 10485760 comes from and if this is fixed or whether it depends on something else Has anyone some info on this? Rob On Wed, Dec 8, 2010 at 3:34 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On Wednesday 08 December 2010 5:47:25 am Rob Gansevles wrote: >> Hello, >> >> Does anyone know what the maximum length is for char or varchar >> columns with limit. >> I saw some answers to this same question referring to section 8.3 of >> the docs, but I don't see the actual numbers there. >> >> I know you can go to 1GB if you don't specify the limit, but I would >> like to know what the max limit is you can use. >> >> It seems to be 10485760 in my install. >> Is this fixed in PostgreSQL or does it depend on some configuration >> setting? Does it depend on the version (I am using 8.4)? >> >> >> Here is my test-sql: >> >> create table test (x varchar(100000000)) >> >> Error: ERROR: length for type varchar cannot exceed 10485760 >> SQLState: 22023 >> ErrorCode: 0 >> >> Thanks in advance, >> >> Rob > > I think you are looking for this: > http://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table.2C_and_a_database.3F > > -- > Adrian Klaver > adrian.klaver@gmail.com >
On Wednesday 08 December 2010 7:06:07 am Rob Gansevles wrote: > Adrian, > > Thanks for the reply, but this refers to max row or field size, it > does not tell me where the max varchar limit of 10485760 comes from > and if this is fixed or whether it depends on something else > > Has anyone some info on this? > > Rob In varchar(n) the n is length of character not bytes. The best description of what that means is from section 8.3 "The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which includes the space padding in the case of character. Longer strings have 4 bytes of overhead instead of 1. Long strings are compressed by the system automatically, so the physical requirement on disk might be less. Very long values are also stored in background tables so that they do not interfere with rapid access to shorter column values. 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.) " So the answer is, it depends on your encoding. -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.com> writes:
> So the answer is, it depends on your encoding.
No, it doesn't. What Rob is looking for is this bit in htup.h:
/*
* MaxAttrSize is a somewhat arbitrary upper limit on the declared size of
* data fields of char(n) and similar types. It need not have anything
* directly to do with the *actual* upper limit of varlena values, which
* is currently 1Gb (see TOAST structures in postgres.h). I've set it
* at 10Mb which seems like a reasonable number --- tgl 8/6/00.
*/
#define MaxAttrSize (10 * 1024 * 1024)
The rationale for having a limit of this sort is (a) we *don't* want
the upper limit of declarable length to be encoding-dependent; and
(b) if you are trying to declare an upper limit that's got more than a
few digits in it, you almost certainly ought to not be declaring a limit
at all.
regards, tom lane
Thanks Tom, this is very helpful. Rob
On 12/08/2010 08:04 AM, Tom Lane wrote: > Adrian Klaver<adrian.klaver@gmail.com> writes: >> So the answer is, it depends on your encoding. > > No, it doesn't. What Rob is looking for is this bit in htup.h: > > /* > * MaxAttrSize is a somewhat arbitrary upper limit on the declared size of > * data fields of char(n) and similar types. It need not have anything > * directly to do with the *actual* upper limit of varlena values, which > * is currently 1Gb (see TOAST structures in postgres.h). I've set it > * at 10Mb which seems like a reasonable number --- tgl 8/6/00. > */ > #define MaxAttrSize (10 * 1024 * 1024) > > The rationale for having a limit of this sort is (a) we *don't* want > the upper limit of declarable length to be encoding-dependent; and > (b) if you are trying to declare an upper limit that's got more than a > few digits in it, you almost certainly ought to not be declaring a limit > at all. > > regards, tom lane Well that explains it :) Would it be possible to change the below section in the docs to state that the declared max value of n is limited to a max string size of 10Mb? I have always taken it to mean that the max value was calculated based off the encoding. Then again it might just be me. "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." -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.com> writes:
> On 12/08/2010 08:04 AM, Tom Lane wrote:
>> The rationale for having a limit of this sort is (a) we *don't* want
>> the upper limit of declarable length to be encoding-dependent; and
>> (b) if you are trying to declare an upper limit that's got more than a
>> few digits in it, you almost certainly ought to not be declaring a limit
>> at all.
> Well that explains it :) Would it be possible to change the below
> section in the docs to state that the declared max value of n is limited
> to a max string size of 10Mb?
I don't really see any point in that. The value is meant to be an order
of magnitude or so more than anything that's sane according to point (b).
If you think you need to know what it is, you're already doing it wrong.
regards, tom lane
On 12/08/2010 09:05 AM, Tom Lane wrote: > Adrian Klaver<adrian.klaver@gmail.com> writes: >> On 12/08/2010 08:04 AM, Tom Lane wrote: >>> The rationale for having a limit of this sort is (a) we *don't* want >>> the upper limit of declarable length to be encoding-dependent; and >>> (b) if you are trying to declare an upper limit that's got more than a >>> few digits in it, you almost certainly ought to not be declaring a limit >>> at all.ion > >> Well that explains it :) Would it be possible to change the below >> section in the docs to state that the declared max value of n is limited >> to a max string size of 10Mb? > > I don't really see any point in that. The value is meant to be an order > of magnitude or so more than anything that's sane according to point (b). > If you think you need to know what it is, you're already doing it wrong. > > regards, tom lane Well the determination of sanity is often in the eye of the beholder and it would be nice to know where the line is. At any rate the answer is the archives now, so I know where to get it. -- Adrian Klaver adrian.klaver@gmail.com
On 2010-12-08, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Adrian Klaver <adrian.klaver@gmail.com> writes: >> On 12/08/2010 08:04 AM, Tom Lane wrote: >>> The rationale for having a limit of this sort is (a) we *don't* want >>> the upper limit of declarable length to be encoding-dependent; and >>> (b) if you are trying to declare an upper limit that's got more than a >>> few digits in it, you almost certainly ought to not be declaring a limit >>> at all. > >> Well that explains it :) Would it be possible to change the below >> section in the docs to state that the declared max value of n is limited >> to a max string size of 10Mb? > > I don't really see any point in that. The value is meant to be an order > of magnitude or so more than anything that's sane according to point (b). > If you think you need to know what it is, you're already doing it wrong. I have some values of perhaps 20Mb that I might want to store samples of in a partitioned table. (so I can delete them easily) what's the right way? I guess I could just keep them as disk files and rotate the directories as I rotate partitions. -- ⚂⚃ 100% natural
2010/12/19 Jasen Betts <jasen@xnet.co.nz>
I have some values of perhaps 20Mb that I might want to store samples
of in a partitioned table. (so I can delete them easily) what's the right way?
20 Mbits or 20 MBytes?
how big samples?
answer to your question depends on what you want to do with these samples in SQL.
if you could give more background information...
I guess I could just keep them as disk files and rotate the
directories as I rotate partitions.
if only reason is "to store" - then files are probably best idea.
PS. why did you post a reply to this thread? It does not seem related