Thread: Alter table syntax

Hello,

Can anyone remind me how to make a column 'not null' after it is created?

Thanks in advance,
Garo.

On Tue, Oct 22, 2002 at 11:38:33PM -0700, Garo Hussenjian wrote:
> Hello,
>
> Can anyone remind me how to make a column 'not null' after it is created?

ALTER TABLE ... ADD NOT NULL
But I think this is a 7.3 addition (i.e. not released yet).

If you don't have that, you can try looking up it's pg_attribute tuple
(it was shown here in the last week how to do so, AFAIR) and set to true
its attnotnull.

Be warned that incorrectly modifying system catalogs can lead to massive
data loss!  Are you scared already?  If so, take all needed precautions
and go ahead.  If you don't, start imagining people yelling at you
because of a lost database and start again.

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"La espina, desde que nace, ya pincha" (Proverbio africano)

Garo Hussenjian wrote:
----------------->>>>
Can anyone remind me how to make a column 'not null' after it is created?
----------------->>>>
From an earlier email (search the archives for more)

HTH

<snip>................
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 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
................<snip>