From oldiesmann@oldiesmann.us Tue Aug  7 17:21:23 2007
Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 7E4F29FB289
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  7 Aug 2007 14:21:23 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 57947-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue,  7 Aug 2007 14:21:15 -0300 (ADT)
X-Greylist: delayed 00:27:01.762567 by SQLgrey-1.7.5
Received: from leto.pronameservice.net (unknown [75.126.142.196])
	by postgresql.org (Postfix) with ESMTP id F09679FB1E4
	for <pgsql-sql@postgresql.org>; Tue,  7 Aug 2007 14:21:16 -0300 (ADT)
Received: from cpe-24-209-224-17.cinci.res.rr.com ([24.209.224.17]:2228
	helo=[192.168.1.100])
	by leto.pronameservice.net with esmtpa (Exim 4.66)
	(envelope-from <oldiesmann@oldiesmann.us>) id 1IISJv-0003Zk-M7
	for pgsql-sql@postgresql.org; Tue, 07 Aug 2007 11:54:11 -0500
Message-ID: <46B8A3AD.6040100@oldiesmann.us>
Date: Tue, 07 Aug 2007 12:54:05 -0400
From: Michael Eshom <oldiesmann@oldiesmann.us>
User-Agent: Thunderbird 2.0.0.4 (Windows/20070604)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Adding NOT NULL columns in PostgreSQL 7.4?
Content-Type: multipart/alternative;
	boundary="------------030106090206060100080004"
X-AntiAbuse: This header was added to track abuse,
	please include it with any abuse report
X-AntiAbuse: Primary Hostname - leto.pronameservice.net
X-AntiAbuse: Original Domain - postgresql.org
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - oldiesmann.us
X-Source: 
X-Source-Args: 
X-Source-Dir: 
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.102 tagged_above=0 required=5 tests=BAYES_50=0.001,
	HTML_MESSAGE=0.001, RDNS_NONE=0.1
X-Spam-Level: 
X-Archive-Number: 200708/39
X-Sequence-Number: 28977

This is a multi-part message in MIME format.
--------------030106090206060100080004
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

Michael Eshom
Christian Oldies Fan
Cincinnati, Ohio


--------------030106090206060100080004
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
</head>
<body bgcolor="#ffffff" text="#000000">
<font size="-1"><font face="Arial">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.<br>
</font></font>
<div class="moz-signature">-- <br>
<p><font color="#0000ff" face="Comic Sans MS">Michael Eshom<br>
Christian Oldies Fan<br>
Cincinnati, Ohio</font></p>
</div>
</body>
</html>

--------------030106090206060100080004--



From grzm@seespotcode.net Tue Aug  7 17:49:57 2007
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