Adding constraint

Hi Patrick

Can you alter a table and drop a column?

Not with an ALTER TABLE statement. You have to create a new table without
this column, fill it with the existing data, drop the original table and
rename the newly created table to the original name.

CREATE TABLE newname AS SELECT <fieldlist> FROM originaltable ...
creates and fills a new table with the results of a SELECT.

Can you add 'not null' to an existing column?

The 7.2 Documentation says:
In the current implementation of ADD COLUMN, default and NOT NULL clauses
for the new column are not supported.

Regards
Conni

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