Thread: get sequence value of insert command

hi

create sequence mysequence;

create table foo( id integer default nextval('mysequence'), bla text, wombat integer, foobar date, primary key(id)
);

insert into foo (wombat) values (88);

now how do i know the id of my newly inserted element? and
how can this be done in a completely concurrency safe way?


cya
erik

O Erik Thiele έγραψε στις Nov 19, 2004 :

> hi
> 
> create sequence mysequence;
> 
> create table foo(
>   id integer default nextval('mysequence'),
>   bla text,
>   wombat integer,
>   foobar date,
>   primary key(id)
> );
> 
> insert into foo (wombat) values (88);
> 
> now how do i know the id of my newly inserted element? and
> how can this be done in a completely concurrency safe way?

The way to do this is by reading the docs :)

use currval, it is session safe.

> 
> 
> cya
> erik
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 

-- 
-Achilleus

On Nov 19, 2004, at 5:41 PM, Erik Thiele wrote:

> now how do i know the id of my newly inserted element? and
> how can this be done in a completely concurrency safe way?

This is a FAQ (4.15.1, among others). See currval() and nextval() in 
the documentation as well.

Michael Glaesemann
grzm myrealbox com

Achilleus Mantzios wrote:
>>
>>now how do i know the id of my newly inserted element? and
>>how can this be done in a completely concurrency safe way?
> 
> 
> The way to do this is by reading the docs :)
> 
> use currval, it is session safe.

The increase in this question suggests the number of new users has 
increased since 8.0 went into beta-test. It's pretty much the first 
question anyone asks.

I seem to recall it was mine. I made the mistake of assuming it wasn't 
concurrency safe and was gently corrected by one of the community. I 
think it might have been a Tim/Tam Lane. Wonder what happened to him? :-)

--   Richard Huxton  Archonet Ltd

> I seem to recall it was mine. I made the mistake of assuming it wasn't 
> concurrency safe and was gently corrected by one of the community. I 
> think it might have been a Tim/Tam Lane. Wonder what happened to him? :-)

Mmmmm.... tim tams

rgds
Homer

> -----Original Message-----
> From: Erik Thiele [mailto:erik@thiele-hydraulik.de]
> Sent: Friday, November 19, 2004 3:42 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] get sequence value of insert command
> 
> 
> hi
> 
> create sequence mysequence;
> 
> create table foo(
>   id integer default nextval('mysequence'),
>   bla text,
>   wombat integer,
>   foobar date,
>   primary key(id)
> );
> 
> insert into foo (wombat) values (88);
> 
> now how do i know the id of my newly inserted element? and
> how can this be done in a completely concurrency safe way?

CREATE RULE get_pkey_on_insert AS ON INSERT TO foo DO SELECT
currval('mysequence') AS id LIMIT 1;

> 
> 
> cya
> erik
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 

On Fri, 19 Nov 2004 10:57:12 -0500
"Passynkov, Vadim" <Vadim.Passynkov@pathcom.com> wrote:

> > -----Original Message-----
> > From: Erik Thiele [mailto:erik@thiele-hydraulik.de]
> > Sent: Friday, November 19, 2004 3:42 AM
> > To: pgsql-sql@postgresql.org
> > Subject: [SQL] get sequence value of insert command
> > 
> > create sequence mysequence;
> > 
> > create table foo(
> >   id integer default nextval('mysequence'),
> >   bla text,
> >   wombat integer,
> >   foobar date,
> >   primary key(id)
> > );
> > 
> > insert into foo (wombat) values (88);
> > 
> > now how do i know the id of my newly inserted element? and
> > how can this be done in a completely concurrency safe way?
> 
> CREATE RULE get_pkey_on_insert AS ON INSERT TO foo DO SELECT
> currval('mysequence') AS id LIMIT 1;

now that one is really great! you should definitly add it to the
faq. plus an additional explanation why the limit 1 is needed here.

thanks!
erik

> > > create sequence mysequence;
> > > 
> > > create table foo(
> > >   id integer default nextval('mysequence'),
> > >   bla text,
> > >   wombat integer,
> > >   foobar date,
> > >   primary key(id)
> > > );
> > > 
> > > insert into foo (wombat) values (88);
> > > 
> > > now how do i know the id of my newly inserted element? and
> > > how can this be done in a completely concurrency safe way?
> > 
> > CREATE RULE get_pkey_on_insert AS ON INSERT TO foo DO SELECT
> > currval('mysequence') AS id LIMIT 1;
> 
> now that one is really great! you should definitly add it to the
> faq. plus an additional explanation why the limit 1 is needed here.

INSERT INTO foo ( ... ) ( SELECT * FROM foo1 );

> 
> thanks!
> erik
> 

-- 
Vadim Passynkov