Thread: Select nextval problem
Earlier it was suggested I do this:
SELECT nextval('my_sequence') as id
Then do the insert with the sequence and all other operations with the
"id". Sure you might have a few holes in the sequence if you abort an
insert, but this way you dont have to mess with OID's etc.
This looks like the best solution, my only problem is the SQL:
SELECT nextval('prodid_seq') as id;
INSERT INTO product VALUES (nextval('prodid_seq'),'...',...,'');
OK up to this point.
Now how do I SELECT back the record I just inserted.
SELECT * FROM product WHERE prodid = id;
ERROR: Attribute 'id' not found
OK, I'm not very good at SQL yet. How do you put the sequence number in
a place holder, then do the insert, and finally retrieve the record you
just inserted by matching the prodid with the number in the place holder.
Thanks for your help in this matter.
Mark
Ericson Smith wrote:
> You might want to get the sequence before you even do the insert...
>
> SELECT nextval('my_sequence') as id
>
> Then do the insert with the sequence and all other operations with the
> "id". Sure you might have a few holes in the sequence if you abort an
> insert, but this way you dont have to mess with OID's etc.
This looks like the best solution, my only problem is the SQL:
SELECT nextval('prodid_seq') as id;
INSERT INTO product VALUES (nextval('prodid_seq'),'...',...,'');
OK up to this point.
Now how do I SELECT back the record I just inserted.
SELECT * FROM product WHERE prodid = id;
ERROR: Attribute 'id' not found
OK, I'm not very good at SQL yet. How do you put the sequence number in
a place holder, then do the insert, and finally retrieve the record you
just inserted by matching the prodid with the number in the place holder.
Thanks for your help in this matter.
Mark
----- Original Message -----
From: "MT" <mt@open2web.com>
Sent: Friday, November 22, 2002 7:46 PM
> SELECT nextval('prodid_seq') as id;
>
> INSERT INTO product VALUES (nextval('prodid_seq'),'...',...,'');
>
> OK up to this point.
>
> Now how do I SELECT back the record I just inserted.
>
> SELECT * FROM product WHERE prodid = id;
> ERROR: Attribute 'id' not found
>
> OK, I'm not very good at SQL yet. How do you put the sequence number in
> a place holder, then do the insert, and finally retrieve the record you
> just inserted by matching the prodid with the number in the place holder.
I'm not an expert myself, but it seems that your problem is probably more
than pure SQL. I think you wish an automated something (a client program,
for example) to do the SELECT and work with its result. Tell us more (is it
C, plpgsql or something else?)
1. SELECT nextval(...) increases prodid_seq, so calling nextval(...) in
INSERT increases it again, causing the one you selected into id (which could
only be seen in that query's result set as attribute 'id') to be lost. So
the first SELECT is not needed.
2. There is something called currval(...) that doesn't increase the counter,
just returns its current value. In our lucky case, you need something that
can be queried with this function, so I'd say either of the following will
do:
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.
3. In plpgsql, use "SELECT INTO _id nextval(...);" where _id is a local
variable, and use it in the select.
4. In C, use whatever tools you have in your C version, etc.
HTH,
G.
--
while (!asleep()) sheep++;
---------------------------- cut here ------------------------------
I use something similar (watch where nextval/currval are used):
SELECT nextval('prodid_seq');
INSERT INTO product VALUES (currval('prodid_seq'),'...',...,'');
SELECT * FROM product WHERE prodid = (SELECT
nextval('prodid_seq'));
But there's probably a better way....
SZUCS Gábor wrote:
>>SELECT nextval('prodid_seq') as id;
>>
>>INSERT INTO product VALUES (nextval('prodid_seq'),'...',...,'');
>>
>>OK up to this point.
>>
>>Now how do I SELECT back the record I just inserted.
>>
>>SELECT * FROM product WHERE prodid = id;
>>ERROR: Attribute 'id' not found
>>
>>OK, I'm not very good at SQL yet. How do you put the sequence number in
>>a place holder, then do the insert, and finally retrieve the record you
>>just inserted by matching the prodid with the number in the place holder.
>>
>>
>
>
>
--
Ron St.Pierre
Syscor R&D
tel: 250-361-1681
email: rstpierre@syscor.com
On Wed, Nov 27, 2002 at 06:15:04PM +0100, SZUCS Gábor wrote:
> 2. There is something called currval(...) that doesn't increase the counter,
> just returns its current value. In our lucky case, you need something that
> can be queried with this function, so I'd say either of the following will
> do:
>
> 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.
Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Support bacteria! They're the only culture some people have.
Attachment
Martijn,
your mail arrived to me as two attachments, with no message body. Could you
do something about this?
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.
G.
--
while (!asleep()) sheep++;
---------------------------- 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.
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.
Dear Martijn, ----- Original Message ----- From: "Martijn van Oosterhout" <kleptog@svana.org> Sent: Friday, November 29, 2002 12:14 AM > 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 use OE, too. And this one is much better than the previous :) > 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. Ah, I see. I keep learning new things, thank you for supporting my studies ;) G. -- while (!asleep()) sheep++; ---------------------------- cut here ------------------------------