Thread: Best practice for altering a table
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi everyone, I've done a fair bit of googling on this, but I haven't come to a conclusion about the recommended procedure for altering some datatypes in a particular table. 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? BTW, I'm running PG 7.1.3 on Debian 3.0. On a related note, I assume that besides being PostgreSQL-specific, the tradeoff for the TEXT datatype's flexibility is speed. If that's correct, how much is the speed penalty on a database that's pretty lightly used? (Few INSERTs, fair number of SELECTs.) - -Tim - -- Tim Wilson Twin Cities, Minnesota, USA Science teacher, Linux fan, Zope developer, Grad. student, Daddy mailto:wilson@visi.com | http://qwerk.org/ | public key: 0x8C0F8813 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE9zzNVB56RQ4wPiBMRAldVAKCTSJ33jGjJLzowDWlqVAaSVl85/gCgtC53 dWQYuq/nUsU0AgbQjuNLfoI= =1IcI -----END PGP SIGNATURE-----
Tim Wilson <wilson@visi.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
Tim, > > 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.) Personally, I'm fond of dumping the who database to text files, editing the text files, and re-loading. This is partly because of the large number of foriegn key constraints, triggers, and views in my databases. -Josh
Tim,
> This sounds appealing. I have a number of foreign keys, sequences, etc. So
> do you dump the whole DB, delete the whole thing, and reload? The key
> question is whether I have to nuke the old DB completely.
Yeah. If it's a production database, I would take the more careful process
of:
1) dump database an edit files (don't forget to edit the data files to match
the new table structure! See the dump options to help with this)
2) copy the dump, and make your edits on the copy.
3) load the edited dump files onto another database or mirror server. Look
for errors during the load process, and then test the loaded database for
problems.
4) if 3) checks out OK, drop the production database and replace it with the
edited load.
5) test the production database; if anything looks wonky, dump and re-load the
unedited files.
I think you'll find that setting up a mirror server (with full versions of the
interface, etc.) is worth the trouble in terms of letting you test changes.
--
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco