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
