Re: TEXT vs VARCHAR

"chris markiewicz" <cmarkiew(at)commnav(dot)com> writes:
> is there a limit on the upper limit of a VARCHAR? i cannot find one in the
> documentation.

The physical limit is circa 1Gb under TOAST. There's a purely arbitrary
limit at 10Mb, which I put in on the theory that "varchar(100000000)"
is probably a typo and certainly pretty silly. (If anyone wants to
argue that decision, feel free --- I just did it on the spur of the
moment while changing the old code that checked for declared size <
BLCKSZ.)

> is it true that a TEXT field can be any size?

TEXT also has a limit at 1Gb. There's really no difference between TEXT
and VARCHAR as far as storage goes. My advice is use VARCHAR(n) if
there is some reason *in the semantics of your application* why the
field should never exceed n characters. If there's not an application-
derived reason for a specific upper limit, declare your field as TEXT
to document that there's no particular limit on it.

> what is the best way to manage memory? for example, if i declare it as
> VARCHAR(10000), does that mean that memory for 10,000 characters will be
> allocated whether i use it or not, or is it dynamic? how about the TEXT
> type.

Either one stores however many characters there are, and no more. Think
of the VARCHAR limit as a constraint check ("length(field) <= n"),
not a storage property.

This is quite unlike CHAR(n), where you get truncation or blank padding
to exactly n characters, so the limit is a storage property as well as
a constraint.

regards, tom lane