Re: Adding constraint [SOLVED]
От | Patrick Nelson |
---|---|
Тема | Re: Adding constraint [SOLVED] |
Дата | |
Msg-id | 4165C48DE9A0D211B6400800095C585F172E7E@WASHINGTON обсуждение исходный текст |
Список | pgsql-general |
Patrick Nelson wrote: ----------------->>>> I added a column to a table with ALTER TABLE company ADD varchar(8); I had really wanted to have this set to not null but now I can't figure out how to do that. At first I thought that I could just drop the column... OK that doesn't seem to be the way. There is a fair amount of data in the tables and I could dump them and rebuild it, but I just get the feeling that there is a easier way. Am I mad? So here are the questions: Can you alter a table and drop a column? Can you add 'not null' to an existing column? Or... I don't know... is there a better way? ----------------->>>> When I first went looking through the archives I didn't find anything. Started thinking that this could not be a first time issue, so I searched again. This time I used just "null" instead of "not null", and was able to find a great email from Joel Burton dtd 22 Apr 2001 in which he goes through a number of similar type scenarios and solutions. I ultimately followed the dump-edit-recreate route which was much simpler (and pretty darn fast also) than I thought it would be. I also went with his process of using a recreate.sql file for this and future changes to any of my databases. Based on that Joel's email, here is the process I followed: 1. Dump the database using: pg_dump -S postgres <database name> > dbdump.sql 2. Created a file called recreate.sql which looks like: DROP DATABASE <database name>; CREATE DATABASE <database name> WITH TEMPLATE=template1; \c <database name> 3. Added a \i recreate.sql to the top of dbdump.sql and then edited table structure in question. 4. Run the command (just want to see errors): echo "\i dbdump.sql" | psql template1 1>/dev/null 5. Re-dump using similar command in step 1 and diff the two dump files for a sanity check
В списке pgsql-general по дате отправления: