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