From: Tom Haddon <tom@betterhealthfoundation.org>
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
To: pgsql-sql@postgresql.org
Subject: Re: ALTER TABLE follow up
Date: Wed, 7 Aug 2002 11:30:53 -0700
Message-ID: <NEBBIHDGCLBEJMPFAMLACENDCHAA.tom@betterhealthfoundation.org> (raw)
In-Reply-To: <GNELIHDDFBOCMGBFGEFOCEIPCDAA.chriskl@familyhealth.com.au>
Hi Chris,
Thanks for that. I have one final question (for the moment).
How do I also alter the id column properties to reflect as below?
DEFAULT nextval('agency_contact_info_id_key'::text)
Is there an SQL query for this, or another manual "twiddle"?
Thanks, Tom
-----Original Message-----
From: Christopher Kings-Lynne [mailto:chriskl@familyhealth.com.au]
Sent: Tuesday, August 06, 2002 7:02 PM
To: Tom Haddon; pgsql-sql@postgresql.org
Subject: RE: [SQL] ALTER TABLE follow up
Hi Tom,
> A follow up to the ALTER TABLE question last week. I had asked why I was
> getting an error message using the ALTER TABLE to add a PRIMARY
> KEY, and was
> told that this wasn't supported in 7.1.x. I've upgraded to 7.2.1 and now
> when I use the following SQL:
>
> ALTER TABLE "agency_contact_info" ADD CONSTRAINT
> "agency_contact_info_pkey"
> PRIMARY KEY NOT NULL ("id");
I have no idea where you got that NOT NULL bit from - it's not in the
manual. In fact Posgres 7.2 has no sql function for changing the null
status of a column. You can manually twiddle the catalogs however - make
SURE there's no NULL values in the column first:
UPDATE pg_attribute SET attnotnull = true WHERE attrelid = (SELECT oid FROM
pg_class WHERE relname = 'agency_contact_info') AND attname = 'id';
Now just go:
ALTER TABLE agency_contact_info ADD PRIMARY KEY (id):
Chris
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: tom@betterhealthfoundation.org, chriskl@familyhealth.com.au
Subject: Re: ALTER TABLE follow up
In-Reply-To: <NEBBIHDGCLBEJMPFAMLACENDCHAA.tom@betterhealthfoundation.org>
* 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