Обсуждение: 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