pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAltering live databases
4+ messages / 2 participants
[nested] [flat]
* Altering live databases
@ 2002-08-07 21:45 Gregory Brauer <greg@wildbrain.com>
2002-08-08 02:38 ` Re: Altering live databases Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 4+ messages in thread
From: Gregory Brauer @ 2002-08-07 21:45 UTC (permalink / raw)
To: pgsql-sql
I am starting to do a test run of maintaining a live database,
and I'm trying to figure out how to add a relation to an existing
table. Lets say I do:
CREATE TABLE something
(
id serial ,
);
And later I decide I *really* wish I had done:
CREATE TABLE something
(
id serial ,
foo_id int4 not null,
foreign key(foo_id) references foo(id) on update CASCADE,
);
How can I update a live database to add the missing relation?
So far I know that I need to:
ALTER TABLE issue_transaction ADD COLUMN foo_id int4 not null;
But how do I declare the foreign key and how do I declare the
ON UPDATE? Is this something that is better done from a
backup? I've noticed the trigger syntax in a backup isn't
exactly friendly, and the TOC Entry IDs are numbered, but I
woudn't know what number to use to create a new one.
Advice?
Greg
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Altering live databases
2002-08-07 21:45 Altering live databases Gregory Brauer <greg@wildbrain.com>
@ 2002-08-08 02:38 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-08 02:52 ` Re: Altering live databases Gregory Brauer <greg@wildbrain.com>
0 siblings, 1 reply; 4+ messages in thread
From: Christopher Kings-Lynne @ 2002-08-08 02:38 UTC (permalink / raw)
To: Gregory Brauer <greg@wildbrain.com>; pgsql-sql
> And later I decide I *really* wish I had done:
>
> CREATE TABLE something
> (
> id serial ,
> foo_id int4 not null,
>
> foreign key(foo_id) references foo(id) on update CASCADE,
> );
>
>
> How can I update a live database to add the missing relation?
>
> So far I know that I need to:
>
> ALTER TABLE issue_transaction ADD COLUMN foo_id int4 not null;
>
>
> But how do I declare the foreign key and how do I declare the
> ON UPDATE? Is this something that is better done from a
> backup? I've noticed the trigger syntax in a backup isn't
> exactly friendly, and the TOC Entry IDs are numbered, but I
> woudn't know what number to use to create a new one.
Your add column above won't work - postgres 7.2 doesn't have a command for
changing the null status of a column (7.3 does), so you need to do this:
ALTER TABLE issue_transaction ADD foo_id int4;
-- make sure there's no null values in the column, and then do this
-- catalog twiddle to set the column not null
UPDATE pg_attribute SET attnotnull = true WHERE attrelid = (SELECT oid FROM
pg_class WHERE relname = 'issue_transaction') AND attname = 'foo_id';
ALTER TABLE issue_transaction ADD FOREIGN KEY (foo_id) REFERENCES foo(id) ON
UPDATE CASCADE;
And you're done.
Chris
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Altering live databases
2002-08-07 21:45 Altering live databases Gregory Brauer <greg@wildbrain.com>
2002-08-08 02:38 ` Re: Altering live databases Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2002-08-08 02:52 ` Gregory Brauer <greg@wildbrain.com>
2002-08-08 02:59 ` Re: Altering live databases Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 4+ messages in thread
From: Gregory Brauer @ 2002-08-08 02:52 UTC (permalink / raw)
To: pgsql-sql
> And you're done.
Indeed I am. Thanks.
One more question... is there yet a way to drop a column from a table?
If not will that come with 7.3?
Greg
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Altering live databases
2002-08-07 21:45 Altering live databases Gregory Brauer <greg@wildbrain.com>
2002-08-08 02:38 ` Re: Altering live databases Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-08 02:52 ` Re: Altering live databases Gregory Brauer <greg@wildbrain.com>
@ 2002-08-08 02:59 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 0 replies; 4+ messages in thread
From: Christopher Kings-Lynne @ 2002-08-08 02:59 UTC (permalink / raw)
To: Gregory Brauer <greg@wildbrain.com>; pgsql-sql
> Indeed I am. Thanks.
>
> One more question... is there yet a way to drop a column from a table?
> If not will that come with 7.3?
You can select the table into a temp table without the column you want
dropped, drop the old one, rename the temp one and recreate all your
indexes, etc. Nasty. However, 7.3 has DROP COLUMN support as of a week
ago.
Chris
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-08-08 02:59 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-07 21:45 Altering live databases Gregory Brauer <greg@wildbrain.com>
2002-08-08 02:38 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-08 02:52 ` Gregory Brauer <greg@wildbrain.com>
2002-08-08 02:59 ` 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