Thread: varchar or text

Hello
I had a look in previous posts in the forum but could not find the
answer I was looking for.
My question is should I switch from varchar to text.
We have "discovered" although it seems to be SQL that adding something
like 'text               ' to a varchar(50) just silently cut the text
while a text with check(length) - or also a varchar with a check raised
an error.
I was suggested to replace varchar(255) with text when we have no idea
on the default length we would define or if we do not want a threshold.
In that case I was thinking about being homogeneous and using text
everywhere adding check where necessary.
I would remove the space strange behavior and would be homogeneous.
I also read the perfs are identical with text and varchar.

My main concern is that if I don't set any check to a text field, it can
be 1Gb large which is a bit tto big to me. Anyway I can't imagine
updating my whole database replacing varchar(255) with text + checks
everywhere. That would make my schema less readable with many checks
where today with \d my display is compact.
I don't know what would be the best ? Keep varchar and live with the
space behavior. Move to text and add checks but the 1Gb limit scares me
a bit.

Thanks for advice or help.

Pascal Cohen a écrit :
> I had a look in previous posts in the forum but could not find the
> answer I was looking for.
> My question is should I switch from varchar to text.
> We have "discovered" although it seems to be SQL that adding something
> like 'text               ' to a varchar(50) just silently cut the text
> while a text with check(length) - or also a varchar with a check raised
> an error.

Nope. If you try to add some text with more than 50 characters on a
varchar(50) column, you will get an error. For example :

test=# create table t (c varchar(5));
CREATE TABLE
test=# insert into t (c) values ('12345');
INSERT 0 1
test=# insert into t (c) values ('123456');
ERREUR:  valeur trop longue pour le type character varying(5)

(the english error message is:
ERROR:  value too long for type character varying(5)
).

Which release do you use ?

> I was suggested to replace varchar(255) with text when we have no idea
> on the default length we would define or if we do not want a threshold.
> In that case I was thinking about being homogeneous and using text
> everywhere adding check where necessary.
> I would remove the space strange behavior and would be homogeneous.
> I also read the perfs are identical with text and varchar.
>

As far as I know, perfs are better with text than with varchar(some
length) because PostgreSQL doesn't have to check the length.

> My main concern is that if I don't set any check to a text field, it can
> be 1Gb large which is a bit tto big to me. Anyway I can't imagine
> updating my whole database replacing varchar(255) with text + checks
> everywhere. That would make my schema less readable with many checks
> where today with \d my display is compact.

I don't see any value in doing that (moving from varchar(some length) to
text with a check constraint on length(col)<=the same length).

> I don't know what would be the best ? Keep varchar and live with the
> space behavior. Move to text and add checks but the 1Gb limit scares me
> a bit.
>

There's no space behavior as you mention it.

If you add a length check on a text column, the limit will be lower than
1GB.

> Thanks for advice or help.
>

Regards.


--
Guillaume.
  http://www.postgresqlfr.org
  http://dalibo.com

Guillaume Lelarge wrote:
> Pascal Cohen a écrit :
>> I had a look in previous posts in the forum but could not find the
>> answer I was looking for.
>> My question is should I switch from varchar to text.
>> We have "discovered" although it seems to be SQL that adding
>> something like 'text               ' to a varchar(50) just silently
>> cut the text while a text with check(length) - or also a varchar with
>> a check raised an error.
>
> Nope. If you try to add some text with more than 50 characters on a
> varchar(50) column, you will get an error. For example :
>
> test=# create table t (c varchar(5));
> CREATE TABLE
> test=# insert into t (c) values ('12345');
> INSERT 0 1
> test=# insert into t (c) values ('123456');
> ERREUR:  valeur trop longue pour le type character varying(5)
>
> (the english error message is:
> ERROR:  value too long for type character varying(5)
> ).
>
> Which release do you use ?
I am with 8.3.1 release but I mentioned that this appears with spaces at
then end not with standard chars. Of course your examples are working
fine but insert something like 'abc        ' (with several spaces and it
will work but just ignore the spaces above the 5th char.

On Tue, Apr 29, 2008 at 09:36:31AM +0200, Pascal Cohen wrote:
> I am with 8.3.1 release but I mentioned that this appears with spaces at
> then end not with standard chars. Of course your examples are working
> fine but insert something like 'abc        ' (with several spaces and it
> will work but just ignore the spaces above the 5th char.

Yeah, the SQL standard has some "interesting" features regarding
char/varchar and trailing spaces. Text doesn't treat spaces specially
at all...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.