Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 1CDB947628E for ; Fri, 18 Oct 2002 16:12:02 -0400 (EDT) Received: from news.hub.org (news.hub.org [64.49.215.80]) by postgresql.org (Postfix) with ESMTP id A4F5D4761C6 for ; Fri, 18 Oct 2002 16:12:00 -0400 (EDT) Received: by news.hub.org (Postfix, from userid 8) id C5C5C381ACB; Fri, 18 Oct 2002 16:11:59 -0400 (EDT) From: Vivek Khera X-Newsgroups: comp.databases.postgresql.sql Subject: adding column with not null constraint Date: 18 Oct 2002 16:12:01 -0400 Organization: Khera Communications, Inc., Rockville, MD Lines: 29 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.0808 (Gnus v5.8.8) XEmacs/21.4 (Honest Recruiter) To: pgsql-sql@postgresql.org X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/248 X-Sequence-Number: 9965 I'm looking to add a column to my database with not null and a default value: vk=> alter table msg_owner add column user_optional_fields varchar(255) NOT NULL default ''; ERROR: Adding columns with defaults is not implemented. Add the column, then use ALTER TABLE SET DEFAULT. vk=> alter table msg_owner add column user_optional_fields varchar(255) NOT NULL ; ERROR: Adding NOT NULL columns is not implemented. Add the column, then use ALTER TABLE ADD CONSTRAINT. Ok, so we can succeed with this: ALTER TABLE msg_owner ADD COLUMN user_optional_fields VARCHAR(255); ALTER TABLE msg_owner ALTER user_optional_fields SET DEFAULT ''; UPDATE msg_owner SET user_optional_fields = ''; Now my problem is I cannot find any syntax for ALTER TABLE ADD CONSTRAINT to put a NOT NULL constraint on a column. Can someone help me here? I'm using Postgres 7.2.1 on FreeBSD 4.6. Thanks. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/