Tom Lane wrote:
"Pierre Thibaudeau" <pierdeux@xxxxxxxxx> 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
