Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id E3A839FBC6F
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  7 Aug 2007 14:49:57 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 65884-06 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  7 Aug 2007 14:49:42 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from mtai05.charter.net (mtai05.charter.net [209.225.8.185])
	by postgresql.org (Postfix) with ESMTP id B06B09FBC29
	for <pgsql-sql@postgresql.org>; Tue,  7 Aug 2007 14:49:52 -0300 (ADT)
Received: from aa02.charter.net ([10.20.200.154]) by mtai05.charter.net
	(InterMail vM.7.08.02.00 201-2186-121-20061213) with ESMTP
	id <20070807174947.IQDE10931.mtai05.charter.net@aa02.charter.net>;
	Tue, 7 Aug 2007 13:49:47 -0400
Received: from [10.0.1.3] (really [66.188.165.91]) by aa02.charter.net
	with ESMTP id <20070807174947.VDTA26124.aa02.charter.net@[10.0.1.3]>;
	Tue, 7 Aug 2007 13:49:47 -0400
In-Reply-To: <46B8A3AD.6040100@oldiesmann.us>
References: <46B8A3AD.6040100@oldiesmann.us>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <E3C783A8-E1D9-421D-A062-DE9195EB04BF@seespotcode.net>
Cc: pgsql-sql@postgresql.org
Content-Transfer-Encoding: 7bit
From: Michael Glaesemann <grzm@seespotcode.net>
Subject: Re: Adding NOT NULL columns in PostgreSQL 7.4?
Date: Tue, 7 Aug 2007 12:49:42 -0500
To: Michael Eshom <oldiesmann@oldiesmann.us>
X-Mailer: Apple Mail (2.752.2)
X-Chzlrs: 0
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200708/40
X-Sequence-Number: 28978


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