Re: varchar(n) VS text

On Jun 27, 2007, at 19:38 , Paul Lambert wrote:

> Is there any disk space advantages to using varchar over text?

No.

> Or will a text field only ever use up as much data as it needs.

Yes.

From http://www.postgresql.org/docs/8.2/interactive/datatype-
character.html

> The storage requirement for data of these types is 4 bytes plus the
> actual string, and in case of character plus the padding. Long
> strings are compressed by the system automatically, so the physical
> requirement on disk may be less. Long values are also stored in
> background tables so they do not interfere with rapid access to the
> 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 very useful to change this because with
> multibyte character encodings the number of characters and bytes
> can be quite different anyway. 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.)
>
> Tip: There are no performance differences between these three
> types, apart from the increased storage size when using the blank-
> padded type. While character(n) has performance advantages in some
> other database systems, it has no such advantages in PostgreSQL. In
> most situations text or character varying should be used instead.

> then would it be better for me to convert these fields to text?.

Probably not. See above.

> 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

Works for me:

test=# select version();

version
------------------------------------------------------------------------
----------------------------------------------------------------------
PostgreSQL 8.2.4 on powerpc-apple-darwin8.9.0, compiled by GCC
powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc.
build 5367)
(1 row)

test=# create table tester (test_varchar varchar primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"tester_pkey" for table "tester"
CREATE TABLE
test=# insert into tester (test_varchar) values ('abc''test');
INSERT 0 1
test=# select * from tester;
test_varchar
--------------
abc'test
(1 row)

> 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?

It's probably not worth the effort, but if you're interested you
could query the system catalogs for varchar columns and write a
script that would update them for you.

Michael Glaesemann
grzm seespotcode net