Обсуждение: 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/