Thread: maximal length of varchar
i'm interested in knowing how the varchar type is limited. since there are no references to this it seems to me, as if there was no postgres-internal limit. is this limit posed just by the os/filesystem/memory? thanks in advance, guenther mair ______________________________________________________ web.by - guenther mair via negrelli 14 negrellistr. - bolzano 39100 bozen via leopardi 11 leopardistr. - merano 39012 meran italia . italy . italien phone +39.0471.568 124 - fax +39.0471.568 129 http://www.web.by.com/ - mail guenther.mair@web.by.com ______________________________________________________
prova=> create table a1(a varchar(99999));
ERROR: length for type 'varchar' cannot exceed 8064
prova=> create table a1(a varchar(8064));
CREATE
prova=> drop table a1;
DROP
prova=> create table a1(a varchar(8064),b varchar(8064));
CREATE
prova=> \d a1
Table = a1
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| a | varchar() | 8064 |
| b | varchar() | 8064 |
+----------------------------------+----------------------------------+-------+
prova=>
ERROR: length for type 'varchar' cannot exceed 8064
prova=> create table a1(a varchar(8064));
CREATE
prova=> drop table a1;
DROP
prova=> create table a1(a varchar(8064),b varchar(8064));
CREATE
prova=> \d a1
Table = a1
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| a | varchar() | 8064 |
| b | varchar() | 8064 |
+----------------------------------+----------------------------------+-------+
prova=>
guenther mair ha scritto:
i'm interested in knowing how the varchar type is
limited. since there are no references to this it
seems to me, as if there was no postgres-internal
limit.is this limit posed just by the os/filesystem/memory?
thanks in advance,
guenther mair
______________________________________________________
web.by - guenther mair
via negrelli 14 negrellistr. - bolzano 39100 bozen
via leopardi 11 leopardistr. - merano 39012 meran
italia . italy . italien
phone +39.0471.568 124 - fax +39.0471.568 129
http://www.web.by.com/ - mail guenther.mair@web.by.com
______________________________________________________
--
______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'
> prova=> create table a1(a varchar(99999)); > ERROR: length for type 'varchar' cannot exceed 8064 > prova=> create table a1(a varchar(8064)); > CREATE thanks, for this quick answer! i did the test by myself and found out, that on the my system (freebsd 3.2) where i do actualy my tests, varchar is limited to 4096. this creates the problem of plattfrom dependence. what length has the text field? -> i tried a insert-command with some 10000 signs for one text-field -> i got a "PQsendQuery() -- query is too long. Maximum length is 8191" hmpf. so maximum length may not exceed 8191 signs in any case. this causes textfields to be reduced to a variable length, which depends on the amount of your table-fields; just think of these two cases: a table with 50 fields and a realy big textfield a table with 2 fields where one is a realy big textfield in the first case the textfield has to be smaller, since i can't exceed the maximum query length of 8191 signs..... now what? :) thanks, guenther
> > > prova=> create table a1(a varchar(99999)); > > ERROR: length for type 'varchar' cannot exceed 8064 > > prova=> create table a1(a varchar(8064)); > > CREATE > > thanks, for this quick answer! > > i did the test by myself and found out, that on the my system (freebsd > 3.2) where i do actualy my tests, varchar is limited to 4096. this creates > the problem of plattfrom dependence. I changed This in 6.5 to something near 8k. It may be too large because I am not sure how a row is going to be updated inside the page. Not sure if this is an issue. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026