Thread: ALTER TABLE follow up
Hi Folks, 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 get an error message saying that "Existing attribute "id" cannot be a PRIMARY KEY because it is not marked NOT NULL" How do I mark this as a NOT NULL to avoid the error message. The column in question is an INT4 field that has been "acquired" through a SELECT INTO statement. Is there some way that I can specify the qualities (NOT NULL, etc.) of the fields in a SELECT INTO statements? Thanks, Tom _______________________________ Tom Haddon IT Director The Better Health Foundation 414 Thirteenth Street, Suite 450 Oakland, CA 94612 (510) 444-5096 www.betterhealthfoundation.org _______________________________
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
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
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@betterhealthfoundation.org] > Sent: Thursday, 8 August 2002 2:31 AM > To: Christopher Kings-Lynne; pgsql-sql@postgresql.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@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 " " 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 > >