Postgres Pro
Downloads
Home   >   mailing lists

Thread: Is this possible?

Is this possible?

From
johnf
Date:
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

Re: Is this possible?

From
"A. Kretschmer"
Date:
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

Re: Is this possible?

From
Scott Marlowe
Date:
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.

Re: Is this possible?

From
johnf
Date:
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

Re: Is this possible?

From
"A. Kretschmer"
Date:
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