Re: [Solved] SQL Server to PostgreSQL

"Jeffrey A. Rhines" <jrhines(at)email(dot)com> writes:
>> Uh ... what's wrong with varchar(n) ?
>
> I've wondered that myself, actually. What are the benefits and
> drawbacks to going with one over the other, besides the obvious 255-char
> field length limit for varchar?

AFAIK there has *never* been a 255-char limit on char or varchar in
pgsql ... you must be thinking of Some Other DBMS.

The limit for these datatypes in 7.0 and before is BLCKSZ less some
overhead --- ~8000 bytes in a default setup. Beginning in 7.1 it's
an essentially arbitrary number. I set it at 10Mb in current sources,
but there's no strong reason for that number over any other. In theory
it could be up to 1Gb, but as Jan Wieck points out in a nearby thread,
you probably wouldn't like the performance of shoving gigabyte-sized
text values around. We need to think about offering API functions that
will allow reading and writing huge field values in bite-sized chunks.

There's no essential performance difference between char(n), varchar(n),
and text in Postgres, given the same-sized data value. char(n)
truncates or blank-pads to exactly n characters; varchar(n) truncates
if more than n characters; text never truncates nor pads. Beyond that
they are completely identical in storage requirements. Pick one based
on the semantics you want for your application.

regards, tom lane