Thread: text vs varchar(n)
Couple of questions: If ones sets up the db to be UNICODE, does that also apply to the 'text' type? Which is faster read and write, a table with text or varchar types: create table text_table ( id bigint, value text ); or create table varchar_table( id bigint, value varchar(1024) ); Why would one every use varchar(n) instead of text (aside from non-sql standard)? Thanks. Richard
On 17.02.02 21:52 -0800(+0000), Richard Emberson wrote: > Which is faster read and write, a table with text or varchar types: > > ... > > Why would one every use varchar(n) instead of text (aside from non-sql > standard)? > Varchar(n) and text have the same performance. The difference is that varchars are silently cut if they are longer than the limit specified. In most cases you want to stick with text. - Einar Karttunen
Le Lundi 18 Février 2002 06:52, Richard Emberson a écrit : > If ones sets up the db to be UNICODE, does that also apply to the 'text' > type? Yes, encoding is set at database creation (CREATE DATABASE foo WITH ENCODING 'Unicode') for all database. This applies to all tables and views. Unicode is handled as fast as Latin1 with no real impact on performances. When using a Unicode PostgreSQL database, you also need a Unicode backend like Apache/PHP or Java. If your backend is Latin1, like VB6, PostgreSQL will not be able to recode from Unicode to Latin1 transparently. Please also note that some server-side functions do not support unicode yet. Cheers, Jean-Michel POURE
I had always thought that the db would get *some* performance increase simply by knowing that x column in a table would never be longer than n characters, meaning it could allocate space ahead of time for those columns as needed. Is this correct or is there really no benefit to using varchar(n)? I want to clarify because one of my coworkers is considering switching a table he has that is all text fields to all varchar(255) and if there really is no benefit I'll tell him to save his time. Furthermore I'd actually start recomending to people to use text fields rather than varchar(n) if this is true. Robert > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Einar Karttunen > Sent: Monday, February 18, 2002 12:55 AM > To: Richard Emberson > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] text vs varchar(n) > > > On 17.02.02 21:52 -0800(+0000), Richard Emberson wrote: > > Which is faster read and write, a table with text or varchar types: > > > > ... > > > > Why would one every use varchar(n) instead of text (aside from non-sql > > standard)? > > > > Varchar(n) and text have the same performance. The difference is that > varchars are silently cut if they are longer than the limit specified. > In most cases you want to stick with text. > > - Einar Karttunen > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
"Robert Treat" <robertt@auctionsolutions.com> writes:
> I had always thought that the db would get *some* performance increase
> simply by knowing that x column in a table would never be longer than n
> characters, meaning it could allocate space ahead of time for those columns
> as needed. Is this correct or is there really no benefit to using
> varchar(n)?
There is no benefit because there is no such thing as allocation ahead
of time. More, there is a loss of performance on insert/update because
you have to go through the length-constraint-checking code.
> I want to clarify because one of my coworkers is considering switching a
> table he has that is all text fields to all varchar(255) and if there really
> is no benefit I'll tell him to save his time.
As a rule of thumb: if there's not a clear application-defined limit for
a field length, you shouldn't make one up in order to use varchar(n).
Numbers like "255" are surely made up, not driven by application
logic...
regards, tom lane
On 19.02.02 10:30 -0500(+0000), Robert Treat wrote: > I had always thought that the db would get *some* performance increase > simply by knowing that x column in a table would never be longer than n > characters, meaning it could allocate space ahead of time for those columns > as needed. Is this correct or is there really no benefit to using > varchar(n)? > > I want to clarify because one of my coworkers is considering switching a > table he has that is all text fields to all varchar(255) and if there really > is no benefit I'll tell him to save his time. Furthermore I'd actually start > recomending to people to use text fields rather than varchar(n) if this is > true. > Please read part 3.3 from users guide for version 7.2. There is no difference in performance. Both of them need to calculate the length of the input to decide what to do, i.e. to toast or not to toast. In output the size of the string is already known. Note also that size and length of the string may differ in more complex encodings. There are only two scenarios that I am aware of in which varchar is better: * you need to use some other db without text datatype (like mysql (it's text support is broken)) * you want to enforce the length constraint ps. Note how the handling of too large strings has changed from 7.1 to 7.2 - Einar Karttunen