Postgres Pro
Downloads
Home   >   mailing lists

Thread: varchar vs char vs text

varchar vs char vs text

From
Singer Wang
Date:
if I have a column that's gonna be between 5-300 charactors...  should I go with a
a charactor? varchar? or a text?

what's the performance penalty going with a text instead of a varchar... or a char?
I don't need to index it.... nor search based on it..

--
Singer XJ Wang - swang@cs.dal.ca - ICQ: 201253
-------------------------------------------------------------------------
   "WAR stands for We Are Right!"

Re: varchar vs char vs text

From
Jason Earl
Date:
Singer Wang <swang@cs.dal.ca> writes:

> if I have a column that's gonna be between 5-300 charactors...
> should I go with a a charactor? varchar? or a text?
>
> what's the performance penalty going with a text instead of a
> varchar... or a char?  I don't need to index it.... nor search based
> on it..

PostgreSQL's varchar and text are both based on the same internal
type.  Varchar only limits the length of the string that can be
inserted.  Char pads the strings with ' ' to the desired lengh.  In
other words if I were to insert 'Jason' into a char(8) it would be
padded to 'Jason   '.

In other words, use varchar if you have a hard limit that you want to
enforce, char if you want to guarantee string lengths, and text for
everything else.

Jason

Re: varchar vs char vs text

From
"Brett W. McCoy"
Date:
On Tue, 12 Feb 2002, Singer Wang wrote:

> if I have a column that's gonna be between 5-300 charactors...  should I go with a
> a charactor? varchar? or a text?
>
> what's the performance penalty going with a text instead of a varchar... or a char?
> I don't need to index it.... nor search based on it..

I'd go with text.  It's not SQL92, though.  varchar is technically
supposed to have a limit of 255, but I don't think that limit
exists in Postgres.

-- Brett
                                          http://www.chapelperilous.net/
------------------------------------------------------------------------
To say you got a vote of confidence would be to say you needed a vote of
confidence.
        -- Andrew Young

Re: varchar vs char vs text

From
Tom Lane
Date:
"Brett W. McCoy" <bmccoy@chapelperilous.net> writes:
> I'd go with text.  It's not SQL92, though.

Check.

> varchar is technically supposed to have a limit of 255,

Certainly not; the spec says

            The maximum value of <length> is implementation-defined.

There may be implementations that are lame enough to limit it to 255,
but Postgres isn't one of them.  IIRC, we set a rather arbitrary upper
limit of 10000000 on the length (mainly on the theory that anything
larger is either a typo, or you really don't want a limit at all, in
which case you oughta be using text).

At least in 7.2, it also works to say just "varchar" with no length
limit; this is functionally equivalent to "text" except perhaps for
some corner cases involving ambiguous-data-type resolution.  But
this is not SQL-spec-compliant either.

            regards, tom lane

Re: varchar vs char vs text

From
"Brett W. McCoy"
Date:
On Tue, 12 Feb 2002, Tom Lane wrote:

> > I'd go with text.  It's not SQL92, though.
>
> Check.
>
> > varchar is technically supposed to have a limit of 255,
>
> Certainly not; the spec says

I sit corrected. :-)

-- Brett
                                          http://www.chapelperilous.net/
------------------------------------------------------------------------
You can bear anything if it isn't your own fault.
        -- Katharine Fullerton Gerould