sequence value of the record just inserted.

[Ben Kim]

Dear admins,

I have a table whose primary key is a record_id with serial type.

I would like to know, when I insert a new row, what was the value of the
record_id that I just inserted. Since this is a multi user application, I
cannot simply select max of the record_id or currval of the sequence.

I would appreciate an advice.

Regards,
Ben Kim


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[scott.marlowe]

Post by Ben Kim
Dear admins,
I have a table whose primary key is a record_id with serial type.
I would like to know, when I insert a new row, what was the value of the
record_id that I just inserted. Since this is a multi user application, I
cannot simply select max of the record_id or currval of the sequence.
I would appreciate an advice.

You want the functions for sequences:

nextval, currval, and setval:

http://www.postgresql.org/docs/7.4/static/functions-sequence.html

nextval and currval are transactionally safe.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Jim Seymour]

Post by Ben Kim
Dear admins,
I have a table whose primary key is a record_id with serial type.
I would like to know, when I insert a new row, what was the value of the
record_id that I just inserted. Since this is a multi user application, I
cannot simply select max of the record_id or currval of the sequence.

You certainly can use currval();

In session #1 I do...

jseymour=> create table foo3 (bar serial, baz int);
jseymour=> insert into foo3 (baz) values (1);

This will put "1" in for column "bar".

In session #2 I do...

jseymour=> insert into foo3 (baz) values (1);

This will put "2" in for column "bar".

Now back to session #1...

jseymour=> select currval('foo3_bar_seq');
currval
---------
1
(1 row)

And session #2...

jseymour=> select currval('foo3_bar_seq');
currval
---------
2
(1 row)

HTH,
Jim

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

http://archives.postgresql.org

[Ben Kim]

Thanks to those who offered help,

The suggested solutions are,
1. Select nextval('myseq'), then use the nextval as the new id to
insert a new record.
2. Insert a new record, then do select currval('myseq') or select last_val
from myseq; supposed to be safe
3. use transaction to guarantee safety

As for solution 2, I wonder what the scope of a "session" is. If I call a
perl subroutine from a web page (the subroutine opens a db handle and
closes it at the end of the subroutine), would it count as one session?
I'll need to check more but would appreciate it if anyone has a ready
advice on this aspect.

Also, in perl DBI, the solutions all require executing at least 2 sql
statements.

I initially hoped there'd be a way to get the oid or sequence number at
the same time as executing an INSERT (one sql statement), like I get oid
in psql. But I use perl DBI, so am not sure how I can get the oid into a
perl variable.

I appreciate the help.


Regards,
Ben Kim



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Greg Sabino Mullane]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Post by Ben Kim
Also, in perl DBI, the solutions all require executing at least
2 sql statements.

Nothing wrong with that.

Post by Ben Kim
I initially hoped there'd be a way to get the oid or sequence number
at the same time as executing an INSERT (one sql statement), like I
get oid in psql. But I use perl DBI, so am not sure how I can get the
oid into a perl variable.

See the documentation for pg_oid_status in DBD::Pg.

- --
Greg Sabino Mullane ***@turnstep.com
PGP Key: 0x14964AC8 200404142201

-----BEGIN PGP SIGNATURE-----

iD8DBQFAfe07vJuQZxSWSsgRAsFgAKCw3NkMiZiwZb5gjxu1Q+Nj9wwkpACcDj1n
gxSIKAGNJefZaJCmU6+tpgs=
=GLPy
-----END PGP SIGNATURE-----



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend