Re: Adding constraint [SOLVED]

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