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
