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

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

Re: get sequence value of insert command

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


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

http://archives.postgresql.org

Re: get sequence value of insert command

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

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

http://archives.postgresql.org

Re: get sequence value of insert command

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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: get sequence value of insert command

> 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

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

Re: get sequence value of insert command

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: 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.

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

>
> thanks!
> erik
>

--
Vadim Passynkov

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