Обсуждение: Is this possible?
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. -- John Fabiani
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
On Mon, Feb 16, 2009 at 7:36 PM, johnf <jfabiani@yolo.com> wrote: > 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). If the FK is on update cascade just update it. and setval() the sequence to be max(pkid)+1.
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
In response to johnf : > Wow that looks like it will work - thanks. > When you say 'within a transaction' do you mean starting with > "Begin" and using "commit"? Exactly. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net