Re: Select nextval problem

On Thu, Nov 28, 2002 at 01:59:34PM +0100, SZUCS Gábor wrote:
> Martijn,
>
> your mail arrived to me as two attachments, with no message body. Could you
> do something about this?

Odd. There was a message body. I know Outlook Express isn't great, but it
should display a text/plain body when it sees one. This one should be
better.

> I think I wasn't clear enough. Under the term "after", I meant time. So if
> you
>
> INSERT ... nextval... -- #1
> ...
> INSERT ... nextval... -- #(n+1)a, or
> INSERT ... VALUES (currval('...')+k); -- #(n+1)b, where k>0
>
> then neither of the following:
>
> SELECT ... currval...
> SELECT ... ORDER BY id DESC LIMIT 1
>
> won't be able to tell the id of INSERT #1. This is what I meant. I.e.
> 'currval' is guaranteed to have a usable value only right after the INSERT
> in question. It's trivial (for me), I just noted it to make things sure. But
> still, I may be wrong. Feel free to tell me if this explanation is still
> wrong.

I meant to say that the currval() will give you the result of the nextval()
you executed even if *other people* have inserted rows. Obviously if you're
inserting multiple rows yourself, you only get the last one.

Put another way, the currval() will return the value from #1 if all the
other statements where executed in another session.

On the other hand, the ORDER BY/LIMIT will produce the wrong answer if other
people have inserted rows. So don't do that.

> ---------------------------- cut here ------------------------------
> ----- Original Message -----
> From: "Martijn van Oosterhout" <kleptog@svana.org>
> Sent: Thursday, November 28, 2002 12:41 AM
>
> >   SELECT * FROM product WHERE prodid = currval('prodid_seq');
> >   SELECT * FROM product ORDER BY prodid DESC LIMIT 1;
> >
> > Both of these, however, assume that you haven't inserted any rows after
> the
> > one in question.
>
> Wrong. The second one does. The first guarenteed to return what the earlier
> nextval() returned. It is therefore the recommended method. Lookup the
> documentation for more details.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Support bacteria! They're the only culture some people have.