Re: [SQL] how to change the type

Stephan Szabo <sszabo@megazone23.bigpanda.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