Re: Adding NOT NULL columns in PostgreSQL 7.4?

On Aug 7, 2007, at 11:54 , Michael Eshom wrote:

> I'm currently using PostgreSQL 7.4.17, and trying to add a NOT NULL  
> column results in an error about "Adding NOT NULL columns is not  
> implemented". The only reports of this error I can find online  
> relate to PostgreSQL 7.2, and further state that it was supposed to  
> be implemented in PostgreSQL 7.3, so I can't figure out why it's  
> still saying it's not implemented.

You can always check the release notes or the relevant documentation:

http://www.postgresql.org/docs/8.2/interactive/release-8-0.html#AEN82267

> E.29.4.4. Object Manipulation Changes

<snip />
>       Allow ALTER ... ADD COLUMN with defaults and NOT NULL  
> constraints; works per SQL spec (Rod)
>
>       It is now possible for ADD COLUMN to create a column that is  
> not initially filled with NULLs, but with a specified default value.

As a workaround in 7.4, this should work:

* Add the column (without the NOT NULL constraint)
* UPDATE the table with the values you want in the new column
* Add the NOT NULL constraint to the column.

You should be able to wrap this all in a transaction if so desired.

Hope this helps.

Michael Glaesemann
grzm seespotcode net