Skip site navigation (1) Skip section navigation (2)

Re: Using a serial primary key as a foreign key in a second

From: Patrick <flymooney(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Using a serial primary key as a foreign key in a second
Date: 2006-12-24 04:38:05
Message-ID: 458E042D.3040003@gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
>   Nathaniel <naptrel(at)yahoo(dot)co(dot)uk> wrote:
> So, I want to add the company "Looney Tunes" (which is auto-assigned  
> a company_id value), and then extract that id value so that I can use  
> it in the company_id foreign key field of Bugs Bunny's person record.


http://jamesthornton.com/postgres/FAQ/faq-english.html#4.15.2

4.15.2) How do I get the value of a SERIAL insert?

One approach is to retrieve the next SERIAL value from the sequence 
object with the nextval() function before inserting and then insert it 
explicitly. Using the example table in 4.15.1, an example in a 
pseudo-language would look like this:

     new_id = execute("SELECT nextval('person_id_seq')");
     execute("INSERT INTO person (id, name) VALUES (new_id, 'Blaise 
Pascal')");

You would then also have the new value stored in new_id for use in other 
queries (e.g., as a foreign key to the person table). Note that the name 
of the automatically created SEQUENCE object will be named 
<table>_<serialcolumn>_seq, where table and serialcolumn are the names 
of your table and your SERIAL column, respectively.

Alternatively, you could retrieve the assigned SERIAL value with the 
currval() function after it was inserted by default, e.g.,

     execute("INSERT INTO person (name) VALUES ('Blaise Pascal')");
     new_id = execute("SELECT currval('person_id_seq')");

Finally, you could use the OID returned from the INSERT statement to 
look up the default value, though this is probably the least portable 
approach. In Perl, using DBI with Edmund Mergl's DBD::Pg module, the oid 
value is made available via $sth->{pg_oid_status} after $sth->execute().




   I am not sure if this is exactly what you might need. It also does 
not specify if this would cause problems for concurrent users or not. 
Hope it helps out though.

Patrick

In response to

pgsql-novice by date

Next:From: Kevin HunterDate: 2006-12-26 18:36:43
Subject: Partitioning
Previous:From: operationsengineer1Date: 2006-12-21 20:39:42
Subject: Re: Using a serial primary key as a foreign key in a second table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group