Re: Is this possible?

On Monday 16 February 2009 10:32:26 pm A. Kretschmer wrote:
> 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

Wow that looks like it will work - thanks.
When you say 'within a transaction' do you mean starting with
"Begin" and using "commit"?

--
John Fabiani