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

[Michael Glaesemann <grzm(at)seespotcode(dot)net>]

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