Thread: How I can read-back a serial value just inserted?
Dear Sirs, my question is very simple: when I insert a row whith a serial field, a value is automatically generated; how can I know this value, strictly of my row, without the risk of to read the value of another subsequent insertion? Thank you. Domenico
On Mon, Jan 01, 2007 at 18:46:26 +0100, dfx <dfx@dfx.it> wrote: > Dear Sirs, > > my question is very simple: > when I insert a row whith a serial field, a value is automatically > generated; how can I know this value, strictly of my row, without the risk > of to read the value of another subsequent insertion? Use currval before calling nextval again (including implicitly) in the same session. (What other sessions are doing won't change the value you see.) In 8.2 you can use the RETURNING clause to get the value inserted.
dfx wrote:
> Dear Sirs,
>
> my question is very simple:
> when I insert a row whith a serial field, a value is automatically
> generated; how can I know this value, strictly of my row, without the risk
> of to read the value of another subsequent insertion?
>
> Thank you.
>
> Domenico
Hiya,
Not sure if it will help you, but what I do is:
SELECT nextval('pt_seq'::regclass);
Take the returned value and use it in the INSERT statement. So for
example, with the sequence/table:
CREATE SEQUENCE pt_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE pt_seq OWNER TO digimer;
CREATE TABLE part_type (
pt_id int primary key default(nextval('pt_seq')),
pt_name text,
...
);
ALTER TABLE part_type OWNER TO digimer;
I would do (in perl, but other languages should be similar enough):
my $pt_id=$dbh->selectrow_array("SELECT nextval('pt_seq'::regclass)");
$dbh->do("INSERT INTO part_type (pt_id, pt_name...) VALUES ($pt_id,
'$name'...)");
Hope that helps!
Madi
If it's a recent PG: select lastval() See this: http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of dfx Sent: Monday, January 01, 2007 11:46 AM To: pgsql-general@postgresql.org Subject: [GENERAL] How I can read-back a serial value just inserted? Dear Sirs, my question is very simple: when I insert a row whith a serial field, a value is automatically generated; how can I know this value, strictly of my row, without the risk of to read the value of another subsequent insertion? Thank you. Domenico ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
On Thursday 18 January 2007 00:57, Adam Rich wrote: > If it's a recent PG: > > select lastval() > > See this: > > http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html > probably more important that you see this: http://people.planetpostgresql.org/xzilla/index.php?/archives/169-Is-lastval-evil.html :-) -- Robert Treat Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
Robert Treat wrote: > On Thursday 18 January 2007 00:57, Adam Rich wrote: >> If it's a recent PG: >> >> select lastval() >> >> See this: >> >> http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html >> > > probably more important that you see this: > http://people.planetpostgresql.org/xzilla/index.php?/archives/169-Is-lastval-evil.html > > :-) > lastval's main benefit is in aiding the port of MySQL apps (being a drop-in replacement for LAST_INSERT_ID()). without lastval such ports can be really difficult. So it does have its place. :)
Hi, On Thu, 2007-01-18 at 10:01 -0600, Russ Brown wrote: > lastval's main benefit is in aiding the port of MySQL apps (being a > drop-in replacement for LAST_INSERT_ID()). without lastval such ports > can be really difficult. I think one can also use INSERT/UPDATE...RETURNING that appeared in 8.2. Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/