Re: varchar or text

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