Re: Best practice for altering a table

Tim Wilson <wilson(at)visi(dot)com> writes:
> Not surprisingly, I've discovered that some of my VARCHARs are not
> long enough and I need to change them.

> What is "best practice" for this sort of thing?

Do you want something that will still work on Alpha Centauri in the
24th-and-a-half century, or do you just want to get the job done
quickly?

In the former case, creating a new table with the right schema
and INSERT/SELECT'ing into it is pretty bulletproof. (But it
won't help if you'd like to preserve view and foreign-key references
to the original table.)

In the latter case, you can get it done by updating
pg_attribute.atttypmod for the column in question. The secret decoder
ring you need is that for VARCHAR, atttypmod is 4 plus the nominal
column length (eg, for VARCHAR(42), atttypmod is 46). This works
cleanly for increasing the column length; for the decreasing case, it's
your responsibility to ensure there are no entries in the column that
would violate the new limit.

BTW: although CHAR(n) uses the same definition of atttypmod, simply
updating atttypmod doesn't get the job done for CHAR(n), because you
won't have adjusted the physical space padding in the column entries.
You could get that case done in hackerly fashion by updating atttypmod
and then saying
UPDATE mytab SET mycol = mycol
to fix the entries themselves.

As always when hacking catalog entries, it's a good idea to practice on
a scratch database to be sure you've got the details down pat ...

> On a related note, I assume that besides being PostgreSQL-specific, the
> tradeoff for the TEXT datatype's flexibility is speed.

Au contraire ... TEXT and VARCHAR are equivalent except that VARCHAR
expends extra cycles on every INSERT or UPDATE to check that the new
column value meets the width limit. Accordingly, VARCHAR is sometimes
slower than TEXT, and is never faster.

regards, tom lane