From: Michael Glaesemann <grzm@seespotcode.net>
To: Michael Eshom <oldiesmann@oldiesmann.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Adding NOT NULL columns in PostgreSQL 7.4?
Date: Tue, 7 Aug 2007 12:49:42 -0500
Message-ID: <E3C783A8-E1D9-421D-A062-DE9195EB04BF@seespotcode.net> (raw)
In-Reply-To: <46B8A3AD.6040100@oldiesmann.us>
References: <46B8A3AD.6040100@oldiesmann.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: grzm@seespotcode.net, oldiesmann@oldiesmann.us
Subject: Re: Adding NOT NULL columns in PostgreSQL 7.4?
In-Reply-To: <E3C783A8-E1D9-421D-A062-DE9195EB04BF@seespotcode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox