pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedget sequence value of insert command
8+ messages / 6 participants
[nested] [flat]
* get sequence value of insert command
@ 2004-11-19 08:41 Erik Thiele <erik@thiele-hydraulik.de>
0 siblings, 2 replies; 8+ messages in thread
From: Erik Thiele @ 2004-11-19 08:41 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: get sequence value of insert command
@ 2004-11-19 09:20 Michael Glaesemann <grzm@myrealbox.com>
parent: Erik Thiele <erik@thiele-hydraulik.de>
1 sibling, 0 replies; 8+ messages in thread
From: Michael Glaesemann @ 2004-11-19 09:20 UTC (permalink / raw)
To: Erik Thiele <erik@thiele-hydraulik.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: get sequence value of insert command
@ 2004-11-19 09:26 Richard Huxton <dev@archonet.com>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 8+ messages in thread
From: Richard Huxton @ 2004-11-19 09:26 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: Erik Thiele <erik@thiele-hydraulik.de>; pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: get sequence value of insert command
@ 2004-11-19 09:37 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Erik Thiele <erik@thiele-hydraulik.de>
1 sibling, 1 reply; 8+ messages in thread
From: Achilleus Mantzios @ 2004-11-19 09:37 UTC (permalink / raw)
To: Erik Thiele <erik@thiele-hydraulik.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: get sequence value of insert command
@ 2004-11-19 09:49 Iain <iain@mst.co.jp>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 8+ messages in thread
From: Iain @ 2004-11-19 09:49 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: Erik Thiele <erik@thiele-hydraulik.de>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: get sequence value of insert command
@ 2004-11-19 15:57 Passynkov, Vadim <Vadim.Passynkov@pathcom.com>
0 siblings, 1 reply; 8+ messages in thread
From: Passynkov, Vadim @ 2004-11-19 15:57 UTC (permalink / raw)
To: 'Erik Thiele' <erik@thiele-hydraulik.de>; pgsql-sql
> -----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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: get sequence value of insert command
@ 2004-11-20 09:24 Erik Thiele <erik@thiele-hydraulik.de>
parent: Passynkov, Vadim <Vadim.Passynkov@pathcom.com>
0 siblings, 0 replies; 8+ messages in thread
From: Erik Thiele @ 2004-11-20 09:24 UTC (permalink / raw)
To: Passynkov, Vadim <Vadim.Passynkov@pathcom.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: get sequence value of insert command
@ 2004-11-22 14:58 Passynkov, Vadim <Vadim.Passynkov@pathcom.com>
0 siblings, 0 replies; 8+ messages in thread
From: Passynkov, Vadim @ 2004-11-22 14:58 UTC (permalink / raw)
To: 'Erik Thiele' <erik@thiele-hydraulik.de>; Passynkov, Vadim <Vadim.Passynkov@pathcom.com>; +Cc: pgsql-sql
> > > 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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2004-11-22 14:58 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-19 08:41 get sequence value of insert command Erik Thiele <erik@thiele-hydraulik.de>
2004-11-19 09:20 ` Michael Glaesemann <grzm@myrealbox.com>
2004-11-19 09:37 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-11-19 09:26 ` Richard Huxton <dev@archonet.com>
2004-11-19 09:49 ` Iain <iain@mst.co.jp>
2004-11-19 15:57 Re: get sequence value of insert command Passynkov, Vadim <Vadim.Passynkov@pathcom.com>
2004-11-20 09:24 ` Erik Thiele <erik@thiele-hydraulik.de>
2004-11-22 14:58 Re: get sequence value of insert command Passynkov, Vadim <Vadim.Passynkov@pathcom.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox