Thread: varchar vs. text

varchar vs. text

From
Michael Garriss
Date:
I curious about the benefits of a varchar over text.  Is it speed,
size?  If so, how much speed, size?  Is a varchar(64) and a text with 64
chars the same size?  I'm inclined to make all my varchars into text so
I don't have to worry about inserting something to big.

TIA,
Michael


Re: varchar vs. text

From
Tom Lane
Date:
Michael Garriss <mgarriss@earthlink.net> writes:
> I curious about the benefits of a varchar over text.

1. Compliance to the letter of the SQL spec.
2. Ability to define a maximum length, if you happen to feel the need to
   impose a specific maximum length.  (If you don't have a good reason
   to impose any particular max length, this is a minus not a plus,
   because the spec says you have to pick one anyway.)

> Is it speed,

It's a loser on speed because of the extra cycles spent to check the
max length.  Other than those cycles, there is no difference.

            regards, tom lane

Re: varchar vs. text

From
Michael Garriss
Date:
Tom Lane wrote:

>Michael Garriss <mgarriss@earthlink.net> writes:
>
>
>>I curious about the benefits of a varchar over text.
>>
>>
>
>1. Compliance to the letter of the SQL spec.
>2. Ability to define a maximum length, if you happen to feel the need to
>   impose a specific maximum length.  (If you don't have a good reason
>   to impose any particular max length, this is a minus not a plus,
>   because the spec says you have to pick one anyway.)
>
>
>
>>Is it speed,
>>
>>
>
>It's a loser on speed because of the extra cycles spent to check the
>max length.  Other than those cycles, there is no difference.
>
>            regards, tom lane
>
>
>
Thanks for the quick response.

Michael

P.S.  The code I'm writing is going to be ported to MySQL by a friend.
Do you have any idea if TEXT is supported over there?


Re: varchar vs. text

From
Ron Johnson
Date:
On Wed, 2003-09-17 at 22:59, Michael Garriss wrote:
> I curious about the benefits of a varchar over text.  Is it speed,
> size?  If so, how much speed, size?  Is a varchar(64) and a text with 64
> chars the same size?  I'm inclined to make all my varchars into text so
> I don't have to worry about inserting something to big.

An analyst would say that "correctness" is a reason for specifying
a max length.  For example, in t_names, I could stick "War and Peace"
into first_name, but, obviously, that's not "correct".

CREATE TABLE t_names (
first_name   TEXT,
last_name    TEXT );

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"(Women are) like compilers. They take simple statements and
make them into big productions."
Pitr Dubovitch


Re: varchar vs. text

From
Harald Fuchs
Date:
In article <3F69366B.6080902@earthlink.net>,
Michael Garriss <mgarriss@earthlink.net> writes:

> P.S.  The code I'm writing is going to be ported to MySQL by a friend.

Shame on you for having a friend using MySQL ;-)

> Do you have any idea if TEXT is supported over there?

Yes: http://www.mysql.com/doc/en/BLOB.html