Re: Is this possible?

In response to johnf :
> Hi,
> I'm not to sure this is possible.
>
> I need to replace a primary key (pkid) with the value of a different field.
> I have
> pkid = 200
> attendid = 301
>
> I need the pkid = 301
>
> But there may or may not be a pkid that already exist that has the value of
> 301. The attendid is unique and the pkid data type is serial (has a
> sequence).
>
> Thanks for the help.

You can do that within a transaction and dropping the pk-constraint:

test=*# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+--------------------------------------------------
id | integer | not null default nextval('foo_id_seq'::regclass)
i | integer |
Indexes:
"foo_pkey" PRIMARY KEY, btree (id)
"foo_i_key" UNIQUE, btree (i)

test=*# select * from foo;
id | i
----+---
1 | 2
2 | 1
3 | 3
(3 rows)

test=*# alter table foo drop constraint foo_pkey;
ALTER TABLE
test=*# update foo set id=i;
UPDATE 3
test=*# alter table foo add primary key(id);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
ALTER TABLE
test=*# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+--------------------------------------------------
id | integer | not null default nextval('foo_id_seq'::regclass)
i | integer |
Indexes:
"foo_pkey" PRIMARY KEY, btree (id)
"foo_i_key" UNIQUE, btree (i)

test=*# select * from foo;
id | i
----+---
2 | 2
1 | 1
3 | 3
(3 rows)

HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net