Thread: getting back autonumber just inserted
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
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)
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 notfind 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. > > >
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)
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/
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.
THX to all guys, it is working great !! greetZ wes
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/
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/
> 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 ?
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)
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/
>> 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 ;)
> 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
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/
> 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...
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/
Hi Listers, I need to know the number of affected (Inserted, deleted, updated) rows by a Query. In MsSQL I use SELECT @@ROWCOUNT, similar to SELECT @@IDENTITY. Is there any @@RowCount similar statement in PostGres?? 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 >
[Please start a new thread when asking new questions.] On Fri, Jul 08, 2005 at 08:56:56AM -0300, jimmy.olsen wrote: > I need to know the number of affected (Inserted, deleted, updated) > rows by a Query. In MsSQL I use SELECT @@ROWCOUNT, similar to SELECT > @@IDENTITY. Is there any @@RowCount similar statement in PostGres?? Your client interface should have a function to get the row count. In libpq, for example, you can call PQcmdTuples(); in PL/pgSQL you can use GET DIAGNOSTICS. See the documentation for whatever interface you're using. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
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.
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
>
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").
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.
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/ >
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
>
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.
Hi, I have a string containing hexa dump of an 4 bytes integer, like '6AF4805C'. Is it possible to convert it somehow to date type in plpgsql (v8.0.0-rc1)? Doc says that date is represented as 4 bytes internaly. How can I get internal representation of a date type field? So I would be able to produce hexa dump of it like above... Daniel
On Mon, Jul 25, 2005 at 10:54:42 +0200, Daniel Drotos <drdani@mazsola.iit.uni-miskolc.hu> wrote: > Hi, > > I have a string containing hexa dump of an 4 bytes integer, like > '6AF4805C'. Is it possible to convert it somehow to date type in > plpgsql (v8.0.0-rc1)? Doc says that date is represented as 4 bytes > internaly. > > How can I get internal representation of a date type field? So I would > be able to produce hexa dump of it like above... area=> select x'6AF4805C'::integer::abstime; abstime ------------------------2026-11-11 08:49:00-06 (1 row) It looks like the your number probably isn't an offset in seconds from January 1 1970. But with more knowledge about what it is, you should be able to turn it into a date or timestamp.