|
Home > Archive > PostgreSQL JDBC > July 2005 > INSERT and get ID
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Peter.Zoche@materna.de 2005-07-27, 7:27 am |
| Hi!
Say I have done the following:
CREATE SEQUENCE myseq
CREATE TABLE foo (
id INTEGER,
val INTEGER,
PRIMARY KEY( id ) );
Is there an easy way to get the id of the newly inserted data set?
So if I do the following:
String insert = "INSERT INTO foo (id, val)
VALUES( nextval('myseq'), 5 )";
int count = statement.executeUpdate( insert );
I only get the row count of inserted rows. Do I have to query the
database again to get the id? Or is there another way? I have tried
statement.executeUpdate( insert, Statement. RETURN_GENERATED_KEY
S);
but I get an PSQLException saying that the feature of auto-generated keys
is not supported. (using postgresql 8.0 JDBC3) I googled but I did only find
PostgreSQL-
specific hints, no one for JDBC.
Thanks, Peter
---------------------------(end of broadcast)---------------------------
TIP 1: 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
| |
| Dave Cramer 2005-07-27, 9:24 am |
|
On 27-Jul-05, at 8:56 AM, Peter.Zoche@materna.de wrote:
> Hi!
>
> Say I have done the following:
>
> CREATE SEQUENCE myseq
>
> CREATE TABLE foo (
> id INTEGER,
> val INTEGER,
> PRIMARY KEY( id ) );
>
> Is there an easy way to get the id of the newly inserted data set?
> So if I do the following:
>
> String insert = "INSERT INTO foo (id, val)
> VALUES( nextval('myseq'), 5 )";
> int count = statement.executeUpdate( insert );
>
> I only get the row count of inserted rows. Do I have to query the
> database again to get the id? Or is there another way? I have tried
> statement.executeUpdate( insert, Statement. RETURN_GENERATED_KEY
S);
> but I get an PSQLException saying that the feature of auto-
> generated keys
> is not supported. (using postgresql 8.0 JDBC3) I googled but I did
> only find
> PostgreSQL-
> specific hints, no one for JDBC.
you can use select currval('myseq') after using nextval('myseq') this
will retrieve the value recently returned by nextval
in your session ( so it will not be over written by another connection )
>
> Thanks, Peter
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: 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
>
>
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Oliver Jowett 2005-07-27, 9:24 am |
| Peter.Zoche@materna.de wrote:
> String insert = "INSERT INTO foo (id, val)
> VALUES( nextval('myseq'), 5 )";
> int count = statement.executeUpdate( insert );
>
> I only get the row count of inserted rows. Do I have to query the
> database again to get the id?
Currently, yes. You can usually do some trickery with currval() to run
both an INSERT and a SELECT in the same statement and avoid an extra
round-trip.
> Or is there another way? I have tried
> statement.executeUpdate( insert, Statement. RETURN_GENERATED_KEY
S);
> but I get an PSQLException saying that the feature of auto-generated keys
> is not supported.
Right, this really needs server-side support before we can implement it
(INSERT ... RETURNING ... or similar)
-O
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
| |
| Roland Walter 2005-07-27, 9:24 am |
| Peter.Zoche@materna.de schrieb:
> Hi!
>
> Say I have done the following:
>
> CREATE SEQUENCE myseq
>
> CREATE TABLE foo (
> id INTEGER,
> val INTEGER,
> PRIMARY KEY( id ) );
>
> Is there an easy way to get the id of the newly inserted data set?
> So if I do the following:
>
> String insert = "INSERT INTO foo (id, val)
> VALUES( nextval('myseq'), 5 )";
> int count = statement.executeUpdate( insert );
>
You have to do a a "SELECT curval('myseq')" after the insert.
--
Roland Walter phone: +49 (0) 22 25 / 88 2-41 1
MOSAIC SOFTWARE AG fax: +49 (0) 22 25 / 88 2-20 1
Am Pannacker 3 mailto: rwa (at) mosaic-ag (dot) com
D-53340 Meckenheim http://www.mosaic-ag.com
------- L E G A L D I S C L A I M E R ---------
Die Informationen in dieser Nachricht sind vertraulich
und ausschliesslich fuer den Adressaten bestimmt.
Kenntnisnahme durch Dritte ist unzulaessig. Die
Erstellung von Kopien oder das Weiterleiten an weitere,
nicht originaere und benannte Adressaten ist nicht
vorgesehen und kann ungesetzlich sein. Die Meinungen
in dieser Nachricht stellen lediglich die Meinungen
des Senders dar. Falls Sie vermuten, dass diese
Nachricht veraendert wurde, setzen Sie sich mit dem
Absender in Verbindung. Der Absender uebernimmt ohne
weitere Ueberpruefung keine Verantwortung fuer die
Richtigkeit und Vollstaendigkeit des Inhalts. Unbefugte
Empfaenger werden gebeten, die Vertraulichkeit der
Nachricht zu wahren und den Absender sofort ueber
einen Uebertragungsfehler zu informieren.
------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Peter.Zoche@materna.de 2005-07-27, 9:24 am |
| > You have to do a a "SELECT curval('myseq')" after the insert.
I had this in mind, too, but I am not sure if it is really safe if
there are several inserts at the same time...
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
| |
| Csaba Nagy 2005-07-27, 9:24 am |
| It will be perfectly safe, as curval will return you the last value
created in the current session. So you can be sure it's your value
unless you use the same connection concurrently in different threads,
but in that case you would have bigger problems :-)
Cheers,
Csaba.
On Wed, 2005-07-27 at 15:29, Peter.Zoche@materna.de wrote:
>
> I had this in mind, too, but I am not sure if it is really safe if
> there are several inserts at the same time...
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
| |
| Dave Cramer 2005-07-27, 9:24 am |
| Yes, it's absolutely safe!
Dave
On 27-Jul-05, at 9:29 AM, Peter.Zoche@materna.de wrote:
>
> I had this in mind, too, but I am not sure if it is really safe if
> there are several inserts at the same time...
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
| |
| Roland Walter 2005-07-27, 9:24 am |
| Peter.Zoche@materna.de schrieb:
>
>
> I had this in mind, too, but I am not sure if it is really safe if
> there are several inserts at the same time...
>
What do you mean with at the same time?
Different connections see different sequence values. So there is no
need to worry.
If you did the
insertions within the same connection, you have to ask the current value
of the sequence after each INSERT.
--
Roland Walter phone: +49 (0) 22 25 / 88 2-41 1
MOSAIC SOFTWARE AG fax: +49 (0) 22 25 / 88 2-20 1
Am Pannacker 3 mailto: rwa (at) mosaic-ag (dot) com
D-53340 Meckenheim http://www.mosaic-ag.com
------- L E G A L D I S C L A I M E R ---------
Die Informationen in dieser Nachricht sind vertraulich
und ausschliesslich fuer den Adressaten bestimmt.
Kenntnisnahme durch Dritte ist unzulaessig. Die
Erstellung von Kopien oder das Weiterleiten an weitere,
nicht originaere und benannte Adressaten ist nicht
vorgesehen und kann ungesetzlich sein. Die Meinungen
in dieser Nachricht stellen lediglich die Meinungen
des Senders dar. Falls Sie vermuten, dass diese
Nachricht veraendert wurde, setzen Sie sich mit dem
Absender in Verbindung. Der Absender uebernimmt ohne
weitere Ueberpruefung keine Verantwortung fuer die
Richtigkeit und Vollstaendigkeit des Inhalts. Unbefugte
Empfaenger werden gebeten, die Vertraulichkeit der
Nachricht zu wahren und den Absender sofort ueber
einen Uebertragungsfehler zu informieren.
------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Sebastiaan van Erk 2005-07-27, 9:24 am |
| If you're going to do an extra select anyway, I would do it *before* the
insert. Select the nextval from the sequence and use that as the primary
key for the insert.
Greetings,
Sebastiaan
Roland Walter wrote:
> Peter.Zoche@materna.de schrieb:
>
>
> What do you mean with at the same time?
>
> Different connections see different sequence values. So there is no
> need to worry.
>
> If you did the
> insertions within the same connection, you have to ask the current value
> of the sequence after each INSERT.
>
>
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
|
|
|
|
|