Re: How to ALTER a TABLE to change the primary key?

On Oct 26, 2007, at 5:39 , Reg Me Please wrote:

> I'd need to modify the primary key definition in an already
> populated table.
> How can I do it?

Drop the primary key constraint and create a new one. You can do this
inside a transaction.

test=# \d strings
    Table "public.strings"
   Column  | Type | Modifiers
----------+------+-----------
a_string | text | not null
Indexes:
     "strings_pkey" PRIMARY KEY, btree (a_string)

test=# begin; alter table strings drop constraint strings_pkey; alter
table strings add constraint new_pkey primary key (a_string); commit;
BEGIN
ALTER TABLE
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
"new_pkey" for table "strings"
ALTER TABLE
COMMIT
test=# \d strings;
    Table "public.strings"
   Column  | Type | Modifiers
----------+------+-----------
a_string | text | not null
Indexes:
     "new_pkey" PRIMARY KEY, btree (a_string)

Michael Glaesemann
grzm seespotcode net