pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedALTER TABLE follow up
4+ messages / 2 participants
[nested] [flat]
* ALTER TABLE follow up
@ 2002-08-06 22:41 Tom Haddon <tom@betterhealthfoundation.org>
2002-08-07 02:02 ` Re: ALTER TABLE follow up Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Haddon @ 2002-08-06 22:41 UTC (permalink / raw)
To: pgsql-sql
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
_______________________________
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ALTER TABLE follow up
2002-08-06 22:41 ALTER TABLE follow up Tom Haddon <tom@betterhealthfoundation.org>
@ 2002-08-07 02:02 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-07 18:30 ` Re: ALTER TABLE follow up Tom Haddon <tom@betterhealthfoundation.org>
0 siblings, 1 reply; 4+ messages in thread
From: Christopher Kings-Lynne @ 2002-08-07 02:02 UTC (permalink / raw)
To: Tom Haddon <tom@betterhealthfoundation.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ALTER TABLE follow up
2002-08-06 22:41 ALTER TABLE follow up Tom Haddon <tom@betterhealthfoundation.org>
2002-08-07 02:02 ` Re: ALTER TABLE follow up Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2002-08-07 18:30 ` Tom Haddon <tom@betterhealthfoundation.org>
2002-08-08 02:32 ` Re: ALTER TABLE follow up Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Haddon @ 2002-08-07 18:30 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ALTER TABLE follow up
2002-08-06 22:41 ALTER TABLE follow up Tom Haddon <tom@betterhealthfoundation.org>
2002-08-07 02:02 ` Re: ALTER TABLE follow up Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-07 18:30 ` Re: ALTER TABLE follow up Tom Haddon <tom@betterhealthfoundation.org>
@ 2002-08-08 02:32 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 0 replies; 4+ messages in thread
From: Christopher Kings-Lynne @ 2002-08-08 02:32 UTC (permalink / raw)
To: Tom Haddon <tom@betterhealthfoundation.org>; 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@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
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-08-08 02:32 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-06 22:41 ALTER TABLE follow up Tom Haddon <tom@betterhealthfoundation.org>
2002-08-07 02:02 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-07 18:30 ` Tom Haddon <tom@betterhealthfoundation.org>
2002-08-08 02:32 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
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