pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedgetting back autonumber just inserted
30+ messages / 16 participants
[nested] [flat]
* getting back autonumber just inserted
@ 2005-02-03 22:16 lorid <lorid@dri.edu>
0 siblings, 2 replies; 30+ messages in thread
From: lorid @ 2005-02-03 22:16 UTC (permalink / raw)
To: pgsql-sql
I could have sworn I kept a copy of prior emails that discussed how to
get back a value that was just inserted into a autonumber (or in
postgresql case a sequence number)
any help will be appreciated
thanks
Lori
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-02-03 22:16 Scott Marlowe <smarlowe@g2switchworks.com>
parent: lorid <lorid@dri.edu>
1 sibling, 0 replies; 30+ messages in thread
From: Scott Marlowe @ 2005-02-03 22:16 UTC (permalink / raw)
To: lorid <lorid@dri.edu>; +Cc: pgsql-sql
On Thu, 2005-02-03 at 16:16, lorid wrote:
> I could have sworn I kept a copy of prior emails that discussed how to
> get back a value that was just inserted into a autonumber (or in
> postgresql case a sequence number)
If you know the name of the sequence the number came from you can use
currval():
insert into table1 (info) values ('abc');
select currval('table1seq');
Assuming table1seq is the name of the sequence here.
In 8.0 there's a function to do this (I'm not sure of the name, but a
quick search of the 8.0 docs should turn it up.)
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-02-03 22:19 Sean Davis <sdavis2@mail.nih.gov>
parent: lorid <lorid@dri.edu>
1 sibling, 1 reply; 30+ messages in thread
From: Sean Davis @ 2005-02-03 22:19 UTC (permalink / raw)
To: lorid <lorid@dri.edu>; +Cc: pgsql-sql
On Feb 3, 2005, at 5:16 PM, lorid wrote:
> I could have sworn I kept a copy of prior emails that discussed how to
> get back a value that was just inserted into a autonumber (or in
> postgresql case a sequence number)
>
See here:
http://www.postgresql.org/docs/8.0/interactive/functions-
sequence.html#FUNCTIONS-SEQUENCE-TABLE
Sean
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-02-03 22:53 Edmund Bacon <ebacon@onesystem.com>
parent: Sean Davis <sdavis2@mail.nih.gov>
0 siblings, 0 replies; 30+ messages in thread
From: Edmund Bacon @ 2005-02-03 22:53 UTC (permalink / raw)
To: pgsql-sql
Perhaps you meant:
http://www.postgresql.org/docs/8.0/interactive/functions-info.html#FUNCTIONS-INFO-SCHEMA-TABLE
in particular |pg_get_serial_sequence|(table_name, column_name)
Sean Davis wrote:
>
> On Feb 3, 2005, at 5:16 PM, lorid wrote:
>
>> I could have sworn I kept a copy of prior emails that discussed how
>> to get back a value that was just inserted into a autonumber (or in
>> postgresql case a sequence number)
>>
>
> See here:
>
> http://www.postgresql.org/docs/8.0/interactive/functions-
> sequence.html#FUNCTIONS-SEQUENCE-TABLE
>
> Sean
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Edmund Bacon <ebacon@onesystem.com>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-02-04 14:11 Richard_D_Levine@raytheon.com
0 siblings, 0 replies; 30+ messages in thread
From: Richard_D_Levine@raytheon.com @ 2005-02-04 14:11 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: lorid <lorid@dri.edu>; pgsql-sql; pgsql-sql-owner@postgresql.org
I don't work with M$ DBs, but saw that "autonumber" is an M$ concept.
Purely for my own edification, how do you get the most resent value of an
autonumber in M$? I was helping someone out who was using M$ stuff and was
amazed that there was no currval function.
Thanks,
Rick
Scott Marlowe
<smarlowe@g2switchw To: lorid <lorid@dri.edu>
orks.com> cc: pgsql-sql@postgresql.org
Sent by: Subject: Re: [SQL] getting back autonumber just inserted
pgsql-sql-owner@pos
tgresql.org
02/03/2005 05:16 PM
On Thu, 2005-02-03 at 16:16, lorid wrote:
> I could have sworn I kept a copy of prior emails that discussed how to
> get back a value that was just inserted into a autonumber (or in
> postgresql case a sequence number)
If you know the name of the sequence the number came from you can use
currval():
insert into table1 (info) values ('abc');
select currval('table1seq');
Assuming table1seq is the name of the sequence here.
In 8.0 there's a function to do this (I'm not sure of the name, but a
quick search of the 8.0 docs should turn it up.)
---------------------------(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
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-02-04 14:48 Magnus Hagander <mha@sollentuna.net>
0 siblings, 0 replies; 30+ messages in thread
From: Magnus Hagander @ 2005-02-04 14:48 UTC (permalink / raw)
To: Richard_D_Levine@raytheon.com; Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: lorid <lorid@dri.edu>; pgsql-sql; pgsql-sql-owner@postgresql.org
> I don't work with M$ DBs, but saw that "autonumber" is an M$ concept.
> Purely for my own edification, how do you get the most resent
> value of an autonumber in M$? I was helping someone out who
> was using M$ stuff and was amazed that there was no currval function.
I beleive they call it IDENTITY and not autonumber.
You get it using either
SELECT @@IDENTITY
or
SELECT SCOPE_IDENTITY()
depending on if you want the very latest identity or the latest int he
current scope (if you have a trigger inserting records in a different
table, they will differ - @@IDENTITY will return from the table affected
by the trigger, SCOPE_IDENTITY() will return it for the table *you*
updated)
//Magnus
^ permalink raw reply [nested|flat] 30+ messages in thread
* getting back autonumber just inserted
@ 2005-07-07 17:50 mail TechEvolution <mail@techevolution.be>
0 siblings, 2 replies; 30+ messages in thread
From: mail TechEvolution @ 2005-07-07 17:50 UTC (permalink / raw)
To: pgsql-sql
hello
i ame a newbie to PostGreSQL, I ame using PostGreSQL 8.0
(windowsinstaller) on a xp prof platform
i would like to get back the autonumber from the last record inserted,
for other SQL db (m$ sql db ...) i could use:
SELECT @@ IDENTITY
can someone help me by informing me what the SQL syntax is to be used
with PostGreSQL db and get the same result, the last autonumber inserted?
greeTz
wes
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-07 18:03 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: mail TechEvolution <mail@techevolution.be>
1 sibling, 2 replies; 30+ messages in thread
From: Alvaro Herrera @ 2005-07-07 18:03 UTC (permalink / raw)
To: mail TechEvolution <mail@techevolution.be>; +Cc: pgsql-sql
On Thu, Jul 07, 2005 at 07:50:16PM +0200, mail TechEvolution wrote:
> hello
>
> i ame a newbie to PostGreSQL, I ame using PostGreSQL 8.0
> (windowsinstaller) on a xp prof platform
>
> i would like to get back the autonumber from the last record inserted,
> for other SQL db (m$ sql db ...) i could use:
> SELECT @@ IDENTITY
>
> can someone help me by informing me what the SQL syntax is to be used
> with PostGreSQL db and get the same result, the last autonumber inserted?
You use the currval() function, using the name of the involved sequence
as parameter. There is a pg_get_serial_sequence() function, to which
you give the table name and column name, and it will give you the
sequence name.
--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
Oh, oh, las chicas galacianas, lo harán por las perlas,
¡Y las de Arrakis por el agua! Pero si buscas damas
Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck)
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-07 18:21 mail TechEvolution <mail@techevolution.be>
parent: Alvaro Herrera <alvherre@alvh.no-ip.org>
1 sibling, 1 reply; 30+ messages in thread
From: mail TechEvolution @ 2005-07-07 18:21 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; pgsql-sql
hi Alvaro Herrera
>> You use the currval() function, using the name of the involved sequence
>> as parameter. There is a pg_get_serial_sequence() function, to which
>> you give the table name and column name
thx for the information
and is there a function i can use to get the last added autonumber without knowing wich table or row? (exactly as select @@ IDENTITY does?) i have read in the messages that there must be such a function for version 8.0, but i could not find it in the documentation, any idea on it?
greetZ
wes
Alvaro Herrera schreef:
>On Thu, Jul 07, 2005 at 07:50:16PM +0200, mail TechEvolution wrote:
>
>
>>hello
>>
>>i ame a newbie to PostGreSQL, I ame using PostGreSQL 8.0
>>(windowsinstaller) on a xp prof platform
>>
>>i would like to get back the autonumber from the last record inserted,
>>for other SQL db (m$ sql db ...) i could use:
>>SELECT @@ IDENTITY
>>
>>can someone help me by informing me what the SQL syntax is to be used
>>with PostGreSQL db and get the same result, the last autonumber inserted?
>>
>>
>
>You use the currval() function, using the name of the involved sequence
>as parameter. There is a pg_get_serial_sequence() function, to which
>you give the table name and column name, and it will give you the
>sequence name.
>
>
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-07 19:15 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: mail TechEvolution <mail@techevolution.be>
0 siblings, 0 replies; 30+ messages in thread
From: Alvaro Herrera @ 2005-07-07 19:15 UTC (permalink / raw)
To: mail TechEvolution <mail@techevolution.be>; +Cc: pgsql-sql
On Thu, Jul 07, 2005 at 08:21:12PM +0200, mail TechEvolution wrote:
> hi Alvaro Herrera
>
> >>You use the currval() function, using the name of the involved sequence
> >>as parameter. There is a pg_get_serial_sequence() function, to which
> >>you give the table name and column name
>
> thx for the information
>
> and is there a function i can use to get the last added autonumber without
> knowing wich table or row? (exactly as select @@ IDENTITY does?) i have
> read in the messages that there must be such a function for version 8.0,
> but i could not find it in the documentation, any idea on it?
There is no such function in 8.0. There will be in 8.1, however.
--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"El hombre nunca sabe de lo que es capaz hasta que lo intenta" (C. Dickens)
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-07 20:14 Theodore Petrosky <tedpet5@yahoo.com>
parent: mail TechEvolution <mail@techevolution.be>
1 sibling, 3 replies; 30+ messages in thread
From: Theodore Petrosky @ 2005-07-07 20:14 UTC (permalink / raw)
To: mail TechEvolution <mail@techevolution.be>; +Cc: pgsql-sql
you have to use currval inside a transaction...
begin;
insert something that increments the counter;
select currval('sequence_name');
end;
using currval inside a transaction guarantees that the
value is correct for your insert statement and has not
changed by another insert statement.
Ted
--- mail TechEvolution <mail@techevolution.be> wrote:
> hello
>
> i ame a newbie to PostGreSQL, I ame using PostGreSQL
> 8.0
> (windowsinstaller) on a xp prof platform
>
> i would like to get back the autonumber from the
> last record inserted,
> for other SQL db (m$ sql db ...) i could use:
> SELECT @@ IDENTITY
>
> can someone help me by informing me what the SQL
> syntax is to be used
> with PostGreSQL db and get the same result, the last
> autonumber inserted?
>
> greeTz
>
> wes
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
____________________________________________________
Sell on Yahoo! Auctions – no fees. Bid on great items.
http://auctions.yahoo.com/
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-07 20:21 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Theodore Petrosky <tedpet5@yahoo.com>
2 siblings, 1 reply; 30+ messages in thread
From: Scott Marlowe @ 2005-07-07 20:21 UTC (permalink / raw)
To: Theodore Petrosky <tedpet5@yahoo.com>; +Cc: mail TechEvolution <mail@techevolution.be>; pgsql-sql
On Thu, 2005-07-07 at 15:14, Theodore Petrosky wrote:
> you have to use currval inside a transaction...
>
> begin;
> insert something that increments the counter;
> select currval('sequence_name');
> end;
>
> using currval inside a transaction guarantees that the
> value is correct for your insert statement and has not
> changed by another insert statement.
Actually, whether you use it inside a transaction or not, as long as
it's in the same session / connection, it is guaranteed to "do the right
thing."
Test it, you'll see.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-07 20:24 mail TechEvolution <mail@techevolution.be>
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 30+ messages in thread
From: mail TechEvolution @ 2005-07-07 20:24 UTC (permalink / raw)
To: pgsql-sql
THX to all guys, it is working great !!
greetZ
wes
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-07 20:37 Michael Fuhr <mike@fuhr.org>
parent: Theodore Petrosky <tedpet5@yahoo.com>
2 siblings, 1 reply; 30+ messages in thread
From: Michael Fuhr @ 2005-07-07 20:37 UTC (permalink / raw)
To: Theodore Petrosky <tedpet5@yahoo.com>; +Cc: mail TechEvolution <mail@techevolution.be>; pgsql-sql
On Thu, Jul 07, 2005 at 01:14:33PM -0700, Theodore Petrosky wrote:
>
> you have to use currval inside a transaction...
Not true. Have you observed otherwise?
> begin;
> insert something that increments the counter;
> select currval('sequence_name');
> end;
>
> using currval inside a transaction guarantees that the
> value is correct for your insert statement and has not
> changed by another insert statement.
currval() returns the most recently obtained value from the sequence
in the current session, regardless of what other sessions are doing
or whether the current session is in a transaction. See the
documentation and the FAQ:
http://www.postgresql.org/docs/8.0/static/functions-sequence.html
"Notice that because this is returning a session-local value, it
gives a predictable answer whether or not other sessions have
executed nextval since the current session did."
http://www.postgresql.org/docs/faqs.FAQ.html#4.11.3
"4.11.3) Doesn't currval() lead to a race condition with other users?
"No. currval() returns the current value assigned by your session, not by
all sessions."
You can do experiments to confirm what the documentation states.
If you see different behavior then please put together a self-contained
test case and report it as a bug.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-07 20:47 Larry Meadors <larry.meadors@gmail.com>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 2 replies; 30+ messages in thread
From: Larry Meadors @ 2005-07-07 20:47 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql
If you have a trigger on your table that inserts a record in a table
and shares the same sequence, what value do you get back, the
triggered curval, or the currently inserted one?
Being a lazy bum, this is why I still prefer the "get key - insert
record" approach. Less brain power required. ;-)
Larry
On 7/7/05, Michael Fuhr <mike@fuhr.org> wrote:
> On Thu, Jul 07, 2005 at 01:14:33PM -0700, Theodore Petrosky wrote:
> >
> > you have to use currval inside a transaction...
>
> Not true. Have you observed otherwise?
>
> > begin;
> > insert something that increments the counter;
> > select currval('sequence_name');
> > end;
> >
> > using currval inside a transaction guarantees that the
> > value is correct for your insert statement and has not
> > changed by another insert statement.
>
> currval() returns the most recently obtained value from the sequence
> in the current session, regardless of what other sessions are doing
> or whether the current session is in a transaction. See the
> documentation and the FAQ:
>
> http://www.postgresql.org/docs/8.0/static/functions-sequence.html
>
> "Notice that because this is returning a session-local value, it
> gives a predictable answer whether or not other sessions have
> executed nextval since the current session did."
>
> http://www.postgresql.org/docs/faqs.FAQ.html#4.11.3
>
> "4.11.3) Doesn't currval() lead to a race condition with other users?
>
> "No. currval() returns the current value assigned by your session, not by
> all sessions."
>
> You can do experiments to confirm what the documentation states.
> If you see different behavior then please put together a self-contained
> test case and report it as a bug.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-07 20:57 Michael Fuhr <mike@fuhr.org>
parent: Larry Meadors <larry.meadors@gmail.com>
1 sibling, 1 reply; 30+ messages in thread
From: Michael Fuhr @ 2005-07-07 20:57 UTC (permalink / raw)
To: lmeadors@apache.org; +Cc: pgsql-sql
On Thu, Jul 07, 2005 at 02:47:23PM -0600, Larry Meadors wrote:
>
> If you have a trigger on your table that inserts a record in a table
> and shares the same sequence, what value do you get back, the
> triggered curval, or the currently inserted one?
That's a different issue than whether currval() is subject to
interference from other transactions. And just wait until PostgreSQL
8.1 comes out and people start using lastval() -- then it could get
*really* confusing which sequence value you're getting.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-07 22:26 PFC <lists@boutiquenumerique.com>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 2 replies; 30+ messages in thread
From: PFC @ 2005-07-07 22:26 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; lmeadors@apache.org; +Cc: pgsql-sql
> That's a different issue than whether currval() is subject to
> interference from other transactions. And just wait until PostgreSQL
> 8.1 comes out and people start using lastval() -- then it could get
> *really* confusing which sequence value you're getting.
What happens if an INSERT trigger inserts something into another table
which also has a sequence ?
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-07 22:42 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: PFC <lists@boutiquenumerique.com>
1 sibling, 1 reply; 30+ messages in thread
From: Alvaro Herrera @ 2005-07-07 22:42 UTC (permalink / raw)
To: PFC <lists@boutiquenumerique.com>; +Cc: Michael Fuhr <mike@fuhr.org>; lmeadors@apache.org; pgsql-sql
On Fri, Jul 08, 2005 at 12:26:30AM +0200, PFC wrote:
>
> >That's a different issue than whether currval() is subject to
> >interference from other transactions. And just wait until PostgreSQL
> >8.1 comes out and people start using lastval() -- then it could get
> >*really* confusing which sequence value you're getting.
>
> What happens if an INSERT trigger inserts something into another
> table which also has a sequence ?
Using what, lastval()? The app will get very confused, because it'll
get the value from the sequence used in the trigger. Using currval
there is no problem, but you already knew that.
--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Hay quien adquiere la mala costumbre de ser infeliz" (M. A. Evans)
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-07 22:44 Michael Fuhr <mike@fuhr.org>
parent: PFC <lists@boutiquenumerique.com>
1 sibling, 1 reply; 30+ messages in thread
From: Michael Fuhr @ 2005-07-07 22:44 UTC (permalink / raw)
To: PFC <lists@boutiquenumerique.com>; +Cc: lmeadors@apache.org; pgsql-sql
On Fri, Jul 08, 2005 at 12:26:30AM +0200, PFC wrote:
>
> >That's a different issue than whether currval() is subject to
> >interference from other transactions. And just wait until PostgreSQL
> >8.1 comes out and people start using lastval() -- then it could get
> >*really* confusing which sequence value you're getting.
>
> What happens if an INSERT trigger inserts something into another
> table which also has a sequence ?
Do you mean with lastval()? Here's what happens:
CREATE FUNCTION trigfunc() RETURNS trigger AS $$
BEGIN
INSERT INTO bar (x) VALUES (NEW.x);
RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE TABLE foo (id serial PRIMARY KEY, x integer);
CREATE TABLE bar (id serial PRIMARY KEY, x integer);
CREATE TRIGGER footrig BEFORE INSERT ON foo
FOR EACH ROW EXECUTE PROCEDURE trigfunc();
ALTER SEQUENCE bar_id_seq RESTART WITH 50;
INSERT INTO foo (x) VALUES (12345);
SELECT lastval();
lastval
---------
50
(1 row)
SELECT * FROM foo;
id | x
----+-------
1 | 12345
(1 row)
SELECT * FROM bar;
id | x
----+-------
50 | 12345
(1 row)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-07 23:54 PFC <lists@boutiquenumerique.com>
parent: Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 0 replies; 30+ messages in thread
From: PFC @ 2005-07-07 23:54 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Michael Fuhr <mike@fuhr.org>; lmeadors@apache.org; pgsql-sql
>> What happens if an INSERT trigger inserts something into another
>> table which also has a sequence ?
>
> Using what, lastval()? The app will get very confused, because it'll
> get the value from the sequence used in the trigger. Using currval
> there is no problem, but you already knew that.
I knew but I forgot since I use an ORM which handles all the mess ;)
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-07 23:56 PFC <lists@boutiquenumerique.com>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 30+ messages in thread
From: PFC @ 2005-07-07 23:56 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: lmeadors@apache.org; pgsql-sql
> Do you mean with lastval()? Here's what happens:
Hm, interesting, you mean the return value of lastval() also depends if
you set your constraints to deferred or immediate ?
I wond
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-08 01:49 Michael Fuhr <mike@fuhr.org>
parent: PFC <lists@boutiquenumerique.com>
0 siblings, 2 replies; 30+ messages in thread
From: Michael Fuhr @ 2005-07-08 01:49 UTC (permalink / raw)
To: PFC <lists@boutiquenumerique.com>; +Cc: lmeadors@apache.org; pgsql-sql
On Fri, Jul 08, 2005 at 01:56:26AM +0200, PFC wrote:
> >Do you mean with lastval()? Here's what happens:
>
> Hm, interesting, you mean the return value of lastval() also depends
> if you set your constraints to deferred or immediate ?
My mind's ablank trying to contrive a situation where that would
matter. Can you provide an example?
In any case, I simply meant to point out that 8.1's lastval() will
be seductively convenient because you don't have to pass it a
sequence name, but the value it returns might not be the value you
want.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-08 02:09 Larry Meadors <larry.meadors@gmail.com>
parent: Michael Fuhr <mike@fuhr.org>
1 sibling, 0 replies; 30+ messages in thread
From: Larry Meadors @ 2005-07-08 02:09 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: PFC <lists@boutiquenumerique.com>; pgsql-sql
Sounds like M$ SuckQL's @@identity value. ;-)
Larry
On 7/7/05, Michael Fuhr <mike@fuhr.org> wrote:
> On Fri, Jul 08, 2005 at 01:56:26AM +0200, PFC wrote:
> > >Do you mean with lastval()? Here's what happens:
> >
> > Hm, interesting, you mean the return value of lastval() also depends
> > if you set your constraints to deferred or immediate ?
>
> My mind's ablank trying to contrive a situation where that would
> matter. Can you provide an example?
>
> In any case, I simply meant to point out that 8.1's lastval() will
> be seductively convenient because you don't have to pass it a
> sequence name, but the value it returns might not be the value you
> want.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-08 03:03 PFC <lists@boutiquenumerique.com>
parent: Michael Fuhr <mike@fuhr.org>
1 sibling, 2 replies; 30+ messages in thread
From: PFC @ 2005-07-08 03:03 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: lmeadors@apache.org; pgsql-sql
> On Fri, Jul 08, 2005 at 01:56:26AM +0200, PFC wrote:
>> >Do you mean with lastval()? Here's what happens:
>>
>> Hm, interesting, you mean the return value of lastval() also depends
>> if you set your constraints to deferred or immediate ?
>
> My mind's ablank trying to contrive a situation where that would
> matter. Can you provide an example?
It's rather perverse and farfetched, but what would stop one from putting
some insert statements in a function that happens to be called somewhere
inside of a check constraint... although one could agree that it's a bit
shooting oneself in the foot...
> In any case, I simply meant to point out that 8.1's lastval() will
> be seductively convenient because you don't have to pass it a
> sequence name, but the value it returns might not be the value you
> want.
It's the first time I see a MySQLism in postgres !
However I like it, cos it might subvert some MySQL users, and provide
easy answers to The Weekly Question on the mailing list (ie where is
AUTO_INCREMENT) ?
I've just noticed that I forgot a close() somewhere hence my data import
files missed a few chunks at the end and MySQL said "3 warnings, 0 errors"
(if it had failed I would have noticed it a week ago but no, more
0000-00-00:00:00:00 hand pruning for me now). Uh oh, SHOW WARNINGS isn't
implemented in that version. There are warnings, but I won't tell you
where. Somewhere. Hm... grep logs ? yeah, but the error message doesn't
mention the table name... nor what kind of warning it is that is not an
error even though half of the row has stayed in an unclosed() file
buffer...
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-08 03:28 Michael Fuhr <mike@fuhr.org>
parent: PFC <lists@boutiquenumerique.com>
1 sibling, 0 replies; 30+ messages in thread
From: Michael Fuhr @ 2005-07-08 03:28 UTC (permalink / raw)
To: PFC <lists@boutiquenumerique.com>; +Cc: lmeadors@apache.org; pgsql-sql
On Fri, Jul 08, 2005 at 05:03:37AM +0200, PFC wrote:
> >On Fri, Jul 08, 2005 at 01:56:26AM +0200, PFC wrote:
> >>Hm, interesting, you mean the return value of lastval() also depends
> >>if you set your constraints to deferred or immediate ?
> >
> >My mind's ablank trying to contrive a situation where that would
> >matter. Can you provide an example?
>
> It's rather perverse and farfetched, but what would stop one from
> putting some insert statements in a function that happens to be called
> somewhere inside of a check constraint... although one could agree that
> it's a bit shooting oneself in the foot...
PostgreSQL doesn't support deferral of check constraints, only
foreign key constraints. But yeah, if you could do that then
presumably lastval() might return a different sequence's value
depending on whether the constraints were deferred or immediate.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-08 13:27 Bruno Wolff III <bruno@wolff.to>
parent: PFC <lists@boutiquenumerique.com>
1 sibling, 0 replies; 30+ messages in thread
From: Bruno Wolff III @ 2005-07-08 13:27 UTC (permalink / raw)
To: PFC <lists@boutiquenumerique.com>; +Cc: Michael Fuhr <mike@fuhr.org>; lmeadors@apache.org; pgsql-sql
On Fri, Jul 08, 2005 at 05:03:37 +0200,
PFC <lists@boutiquenumerique.com> wrote:
>
> It's the first time I see a MySQLism in postgres !
This has meaning in more ways than one.
> However I like it, cos it might subvert some MySQL users, and
> provide easy answers to The Weekly Question on the mailing list (ie where
> is AUTO_INCREMENT) ?
More likely people will shoot themselves in the foot with this feature and come
to the mailing list with questions that take even more time to figure out
than telling them how to use currval(pg_get_serial_sequence().
IMO it is a bad idea to include this feature and that no sane developer will
make use of it in applications.
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-09 12:59 jimmy.olsen <jimmy.olsen@ig.com.br>
parent: Alvaro Herrera <alvherre@alvh.no-ip.org>
1 sibling, 1 reply; 30+ messages in thread
From: jimmy.olsen @ 2005-07-09 12:59 UTC (permalink / raw)
To: pgsql-sql
I don't know how to create a Global Variable in Postgres, but the idea is
very simple:
1. Encapsulate the NextVal function in MyNextVal
2. Set to Global variable with NextVal of the desired sequence
3. Inspect to value of the global variable (exactally as it's done in MsSQL)
When Creating a Table:
CREATE TABLE tabTest(
codTest Int Not Null PRIMARY KEY DEFAULT(NextVal("seq_test"),
... # Field List
)
Try this:
CREATE TABLE tabTest(
codTest Int Not Null PRIMARY KEY DEFAULT(MyNextVal("seq_test"),
... # Field List
)
this is not the correct sintax, just an algoritm
CREATE FUNCTION MyNextVal(varchar) RETURNS INT AS
$$ SELECT GlobalVar = NextVal($1)
RETURN GlobalVar
$$
Alessandro - Liga One Sistemas
alessandro@ligaone.com.br
----- Original Message -----
From: "Alvaro Herrera" <alvherre@alvh.no-ip.org>
To: "mail TechEvolution" <mail@techevolution.be>
Cc: <pgsql-sql@postgresql.org>
Sent: Thursday, July 07, 2005 3:03 PM
Subject: Re: [SQL] getting back autonumber just inserted
> On Thu, Jul 07, 2005 at 07:50:16PM +0200, mail TechEvolution wrote:
> > hello
> >
> > i ame a newbie to PostGreSQL, I ame using PostGreSQL 8.0
> > (windowsinstaller) on a xp prof platform
> >
> > i would like to get back the autonumber from the last record inserted,
> > for other SQL db (m$ sql db ...) i could use:
> > SELECT @@ IDENTITY
> >
> > can someone help me by informing me what the SQL syntax is to be used
> > with PostGreSQL db and get the same result, the last autonumber
inserted?
>
> You use the currval() function, using the name of the involved sequence
> as parameter. There is a pg_get_serial_sequence() function, to which
> you give the table name and column name, and it will give you the
> sequence name.
>
> --
> Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
> Oh, oh, las chicas galacianas, lo harán por las perlas,
> ¡Y las de Arrakis por el agua! Pero si buscas damas
> Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck)
>
> ---------------------------(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
>
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-10 01:00 Mischa Sandberg <mischa.sandberg@telus.net>
parent: jimmy.olsen <jimmy.olsen@ig.com.br>
0 siblings, 0 replies; 30+ messages in thread
From: Mischa Sandberg @ 2005-07-10 01:00 UTC (permalink / raw)
To: jimmy.olsen <jimmy.olsen@ig.com.br>; +Cc: pgsql-sql
Quoting "jimmy.olsen" <jimmy.olsen@ig.com.br>:
> I don't know how to create a Global Variable in Postgres, but the
> idea is
> very simple:
> 1. Encapsulate the NextVal function in MyNextVal
> 2. Set to Global variable with NextVal of the desired sequence
> 3. Inspect to value of the global variable (exactally as it's done in
> MsSQL)
Not sure if this thread is about global variables or just how to use
sequences, but ... if you want variables that are global, you can use a
table (global to all processes), a temp table (global to everything
inside one connection), or a row in a global table (can be either). I
tend to keep one global table of (key,value) pairs, with a primary key
constraint so that the query optimizer knows that "SELECT val FROM Env
WHERE key='xxx'" will return at most 1 row. The "keys" are actually
structured paths ("mail.process.thread_limit").
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-13 21:05 Vivek Khera <vivek@khera.org>
parent: Theodore Petrosky <tedpet5@yahoo.com>
2 siblings, 0 replies; 30+ messages in thread
From: Vivek Khera @ 2005-07-13 21:05 UTC (permalink / raw)
To: pgsql-sql
On Jul 7, 2005, at 4:14 PM, Theodore Petrosky wrote:
>
> you have to use currval inside a transaction...
>
> begin;
> insert something that increments the counter;
> select currval('sequence_name');
> end;
>
> using currval inside a transaction guarantees that the
> value is correct for your insert statement and has not
> changed by another insert statement.
>
your understanding of currval() is completely incorrect. no
transaction is required.
Attachments:
smime.p7s (application/pkcs7-signature, 2.4K) download
^ permalink raw reply [nested|flat] 30+ messages in thread
* Re: getting back autonumber just inserted
@ 2005-07-14 13:05 Bruno Wolff III <bruno@wolff.to>
parent: Larry Meadors <larry.meadors@gmail.com>
1 sibling, 0 replies; 30+ messages in thread
From: Bruno Wolff III @ 2005-07-14 13:05 UTC (permalink / raw)
To: lmeadors@apache.org; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-sql
On Thu, Jul 07, 2005 at 14:47:23 -0600,
Larry Meadors <larry.meadors@gmail.com> wrote:
> If you have a trigger on your table that inserts a record in a table
> and shares the same sequence, what value do you get back, the
> triggered curval, or the currently inserted one?
Whichever one was done second. I think it has to be the trigger, because
I think the inserted values will be evaluated first (even before aa
before trigger) since the triggers need to have access to them.
^ permalink raw reply [nested|flat] 30+ messages in thread
end of thread, other threads:[~2005-07-14 13:05 UTC | newest]
Thread overview: 30+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-03 22:16 getting back autonumber just inserted lorid <lorid@dri.edu>
2005-02-03 22:16 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-02-03 22:19 ` Sean Davis <sdavis2@mail.nih.gov>
2005-02-03 22:53 ` Edmund Bacon <ebacon@onesystem.com>
2005-02-04 14:11 Re: getting back autonumber just inserted Richard_D_Levine@raytheon.com
2005-02-04 14:48 Re: getting back autonumber just inserted Magnus Hagander <mha@sollentuna.net>
2005-07-07 17:50 getting back autonumber just inserted mail TechEvolution <mail@techevolution.be>
2005-07-07 18:03 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2005-07-07 18:21 ` mail TechEvolution <mail@techevolution.be>
2005-07-07 19:15 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2005-07-09 12:59 ` jimmy.olsen <jimmy.olsen@ig.com.br>
2005-07-10 01:00 ` Mischa Sandberg <mischa.sandberg@telus.net>
2005-07-07 20:14 ` Theodore Petrosky <tedpet5@yahoo.com>
2005-07-07 20:21 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-07-07 20:24 ` mail TechEvolution <mail@techevolution.be>
2005-07-07 20:37 ` Michael Fuhr <mike@fuhr.org>
2005-07-07 20:47 ` Larry Meadors <larry.meadors@gmail.com>
2005-07-07 20:57 ` Michael Fuhr <mike@fuhr.org>
2005-07-07 22:26 ` PFC <lists@boutiquenumerique.com>
2005-07-07 22:42 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2005-07-07 23:54 ` PFC <lists@boutiquenumerique.com>
2005-07-07 22:44 ` Michael Fuhr <mike@fuhr.org>
2005-07-07 23:56 ` PFC <lists@boutiquenumerique.com>
2005-07-08 01:49 ` Michael Fuhr <mike@fuhr.org>
2005-07-08 02:09 ` Larry Meadors <larry.meadors@gmail.com>
2005-07-08 03:03 ` PFC <lists@boutiquenumerique.com>
2005-07-08 03:28 ` Michael Fuhr <mike@fuhr.org>
2005-07-08 13:27 ` Bruno Wolff III <bruno@wolff.to>
2005-07-14 13:05 ` Bruno Wolff III <bruno@wolff.to>
2005-07-13 21:05 ` Vivek Khera <vivek@khera.org>
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