Re: [SQL] how to change the type

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> IIRC, the values are stored as length + data. I think char() might
> do wierd things (I don't know if the trailing spaces are stored), but
> varchar() and text should be expandable because anything that could have
> fit before should still fit and look the same.

Yup, exactly.

> Going down is
> problematic, because if you have a varchar(5) field where one value is say
> 'abcd' and you make it varchar(3) what happens?

What would actually happen right now is nothing: the value would still
be 'abcd' and would still read out that way. The 3-char limit would
only get enforced during inserts and updates of the column.

char(N) does store the trailing spaces, so altering N would give
unwanted results: again, existing values would read out with the old
width until updated. You could fix this by issuing

UPDATE tab SET col = col

after tweaking the pg_attribute.atttypmod value. (AFAICS, any "clean"
implementation would have to do just that internally, with the same
unpleasant space and speed implications as we've discussed for DROP
COLUMN.)

regards, tom lane