Thread: Field sizes and structure
This is my first message to the list, I hope it is not so simple that it offends anyone, but the 2 books I have do not seem to answer the questions I have and searching different archives seem to present thousands of posts to review. If possible, once a table is created, how can a field size be changed? And also, the field position in the table? Not that I've had a need for position and can't thing of why it would matter, just thought I'd add that one to see if it is possible in the future. Thanks, Robert
On Mon, 9 Dec 2002, Robert Fitzpatrick wrote: > This is my first message to the list, I hope it is not so simple that it > offends anyone, but the 2 books I have do not seem to answer the > questions I have and searching different archives seem to present > thousands of posts to review. > > If possible, once a table is created, how can a field size be changed? > And also, the field position in the table? Not that I've had a need for > position and can't thing of why it would matter, just thought I'd add > that one to see if it is possible in the future. There are ways of messing about with system tables to change the size of a field. These can have horrible, unpredictable consequences, but many folks seem to be able to do such things and are happy with the results. Backup all data before trying to do anything like that. The other way to do it. Assuming you either have no indexes or foreigh keys (yet) or don't mind recreating them at some later date, you can do it something like this: create table t1 (name varchar(20), address varchar(30), state char(2), zip varchar(5)); insert data here... Now to convert the old table, because you need to handle different lengths: begin; select name::varchar(40), address::varchar(45), state char(4), zip::varchar(10) into t2 from t1; The new table will have no indexes and such, so now would be the time to create them... then.. alter table t1 rename to t1_old; alter table t2 rename to t1; create index... create constraint... commit; (Assuming it all looks good. If it don't then issue a rollback instead) As for changing the order, it's basically the same. You can either use the select into (or create table, insert into) or you can use pg_dump / pg_restore to do it with some editing by hand or a script you can write.
do a \h on alter table (in psql) and you will get a big list of examples of how to alter a table.. so you can change it from say varchar(5) to varchar(25).. I don't think you order matters as you can select what you want.. and what order during the select statement.. so if you have columns a,b,c,d and you want the order to be c,b,a,d just do a select c,b,a,d from whatver and thats the order you will get your information in.. Travis -----Original Message----- From: Robert Fitzpatrick [mailto:robert@webtent.com] Sent: Monday, December 09, 2002 3:44 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Field sizes and structure This is my first message to the list, I hope it is not so simple that it offends anyone, but the 2 books I have do not seem to answer the questions I have and searching different archives seem to present thousands of posts to review. If possible, once a table is created, how can a field size be changed? And also, the field position in the table? Not that I've had a need for position and can't thing of why it would matter, just thought I'd add that one to see if it is possible in the future. Thanks, Robert ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> > begin; > select name::varchar(40), address::varchar(45), state char(4), > zip::varchar(10) into t2 from t1; > Thanks for the response, works great. Is there a way to maintain the Primary key and NOT NULL properties using this method? -- Robert
On Mon, Dec 09, 2002 at 06:04:57PM -0500, Williams, Travis L, NPONS wrote: > do a \h on alter table (in psql) and you will get a big list of examples > of how to alter a table.. so you can change it from say varchar(5) to > varchar(25).. No, that case isn't covered. You have to mess with pg_attribute's atttypmod field for the attribute you want to change, manually. Note that for VARCHAR and the like, it's length+4. Don't try to change it without backups... or you will suffer the consequences (if you backup, however, nothing harmful will happen -- this is a law of nature). -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "El conflicto es el camino real hacia la union"