By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,812 Members | 1,978 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,812 IT Pros & Developers. It's quick & easy.

Insert and obtain the pk

P: n/a


Hello everybody,

Does anyone know an easy way to insert a record (without a value for the
primary key because it is a sequence) and at the same time obtain the value
of primary key that was assign to this new record.

I always try insert and then select with the same values that I used for the
insert, but I believe this is not a good way to do it.

Any suggestions???
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Thu, Nov 06, 2003 at 12:26:48PM -0600, Eliu Montoya wrote:
Does anyone know an easy way to insert a record (without a value for the
primary key because it is a sequence) and at the same time obtain the value
of primary key that was assign to this new record.


Given that you are already using a sequence, you can obtain the last
value given to you by means of currval(). No, this doesn't have any
concurrency problems with multiple backends inserting at the same time;
that's exactly the problem it solves.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"In Europe they call me Niklaus Wirth; in the US they call me Nickel's worth.
That's because in Europe they call me by name, and in the US by value!"

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #2

P: n/a
Eliu Montoya writes:
Does anyone know an easy way to insert a record (without a value for the
primary key because it is a sequence) and at the same time obtain the value
of primary key that was assign to this new record.


If you know the name of the sequence then you can use the function
currval() to get the last assigned value in your session.

--
Peter Eisentraut pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #3

P: n/a
You can call currval on the sequence that provided the value for the
primary key. It's guaranteed to be the last value retrieved from that
sequence *for your session*.

insert into table1 (f1, f2) values ('a', 'b');

select currval('table1_seq');

Use the name of your sequence. If the primary key is a SERIAL type,
then there will be a sequence in the background. I believe it's
tablename_seq or something like that.

regards, adam

On Nov 6, 2003, at 11:26 AM, Eliu Montoya wrote:
*

*

*

Hello everybody,

Does anyone know an easy way to *insert a record (without a value for
the primary key because it is a sequence) *and at the same time obtain
the value of primary key that was assign to this new record.

*

I always try insert and then select with the same values that I used
for the insert, but I believe this is not a good way to do it.

*

Any suggestions???


Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.