Insert and obtain the pk

[Eliu Montoya]

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???

[Alvaro Herrera]

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

[Peter Eisentraut]

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

[Adam Ruth]

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???