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
