Thread: Altering live databases

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

> 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

> 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

> 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