Re: varchar(n) VS text - Mailing list pgsql-general

Tom Lane wrote:
> "Pierre Thibaudeau" <pierdeux@gmail.com> writes:
>> I am puzzling over this issue:
>
>> 1) Is there ever ANY reason to prefer "varchar(n)" to "text" as a column type?
>
> In words of one syllable: no.
>
> Not unless you have an application requirement for a specific maximum
> length limit (eg, your client code will crash if fed a string longer
> than 256 bytes, or there's a genuine data-validity constraint that you
> can enforce this way).
>
> Or if you want to have schema-level portability to some other DB that
> understands varchar(N) but not text.  (varchar(N) is SQL-standard,
> while text isn't, so I'm sure there are some such out there.)
>
>> From my reading of the dataype documentation, the ONLY reason I can
>> think of for using "varchar(n)" would be in order to add an extra
>> data-type constraint to the column.
>
> That is *exactly* what it does.  No more and no less.  There's no
> performance advantage, in fact you can expect to lose a few cycles
> to the constraint check.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org/
>
>

Is there any disk space advantages to using varchar over text? Or will a
text field only ever use up as much data as it needs.

I have a database where pretty much all text-type fields are created as
varchars - I inherited this db from an MS SQL server and left them as
varchar when I converted the database over to PG. My thoughts were text
being a non-constrained data type may use up more disk space than a
varchar and if I know there will never be more than 3 characters in the
field for example, I could save some space by only creating a 3 length
field.

In my case, any field length restrictions are governed by the
application so I don't really need the constraint built into the back
end. If there is a slight performance disadvantage to using varchar and
no real disk space saving - and I have in some cases 40 or 50 of these
fields in a table - then would it be better for me to convert these
fields to text?.

Not to mention that I run into a problem occasionally where inputting a
string that contains an apostraphe - PG behaves differently if it is a
varchar to if it is a text type and my app occasionally fails.

I.e.
insert into tester (test_varchar) values ('abc''test');
I get the following:
ERROR: array value must start with "{" or dimension information
SQL state: 22P02

If I use the same command but inserting into a text-type field.
insert into tester (test_text) values ('abc''test');
It works fine.

But that's beside the point - my question is should I convert everything
to text fields and, if so, is there any easy way of writting a script to
change all varchar fields to text?

--
Paul Lambert
Database Administrator
AutoLedgers