From: | "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au> |
---|---|
To: | "Tom Haddon" <tom(at)betterhealthfoundation(dot)org>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: ALTER TABLE follow up |
Date: | 2002-08-08 02:32:51 |
Message-ID: | GNELIHDDFBOCMGBFGEFOGEJFCDAA.chriskl@familyhealth.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi Tom,
ALTER TABLE agency_contact_info ALTER id SET DEFAULT
nextval('agency_contact_info_id_key'::text);
BTW, Postgres 7.3 will have this command:
ALTER TABLE tab ALTER col SET/DROP NOT NULL;
Chris
> -----Original Message-----
> From: Tom Haddon [mailto:tom(at)betterhealthfoundation(dot)org]
> Sent: Thursday, 8 August 2002 2:31 AM
> To: Christopher Kings-Lynne; pgsql-sql(at)postgresql(dot)org
> Subject: RE: [SQL] ALTER TABLE follow up
>
>
> 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(at)familyhealth(dot)com(dot)au]
> Sent: Tuesday, August 06, 2002 7:02 PM
> To: Tom Haddon; pgsql-sql(at)postgresql(dot)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 " " 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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Christopher Kings-Lynne | 2002-08-08 02:38:26 | Re: Altering live databases |
Previous Message | Gregory Brauer | 2002-08-07 21:45:49 | Altering live databases |