Re: Introducing another primary key field?

On Aug 26, 2004, at 2:05 PM, Bjørn T Johansen wrote:

> I need to introduce an extra field in a table that has one field as
> primary key. The "problem" is that this new field should be combined
> with the existing pk to create a new combined pk for the table, how to
> I change the table?

Inside a transaction, you'll want to drop the old primary key
constraint (ALTER TABLE foo DROP CONSTRAINT ...), add the new column
(ALTER TABLE foo ADD COLUMN newcol ... ), fill it with some kind of
data (UPDATE foo set newcol = ... ), and add the new constraint (ALTER
TABLE foo ADD CONSTRAINT ... )

I think you'll have to set the new column NOT NULL also (ALTER TABLE
foo ALTER COLUMN newcol SET NOT NULL) to be able to make it part of a
key.

Here are some docs that might help you:
<http://www.postgresql.org/docs/current/static/sql-altertable.html>
<http://www.postgresql.org/docs/current/static/ddl-constraints.html>

Hope this helps!

Michael Glaesemann
grzm myrealbox com