Thread: automatic sequence number?
Hello. I have a table with one field acting as primary key, the key is retrieved from a sequence-generator in postgresql. Currently I am executing two queries for every insert; Query #1 retrieves a number from a sequence-generator, and places it into the record. Query #2 performs the actual insert. Does anyone have an idea how to cut this down to only one query? And maybe somehow get back the primary key in the ResultSet. Or maybe this is how it is supposed to be done, it is just that I am not very experienced in jdbc/sql to know. :) Thanks in advance. //ap
On Sun, 2003-11-02 at 09:28, apnv3@muha.net wrote:
> Hello.
>
> I have a table with one field acting as primary key, the key is
> retrieved from a sequence-generator in postgresql.
> Currently I am executing two queries for every insert;
>
> Query #1 retrieves a number from a sequence-generator, and places it into the
> record.
>
> Query #2 performs the actual insert.
>
> Does anyone have an idea how to cut this down to only one query?
> And maybe somehow get back the primary key in the ResultSet.
>
> Or maybe this is how it is supposed to be done, it is just that I am not
> very experienced in jdbc/sql to know. :)
If you don't mind it being a little ugly, send both SQL queries in a
single command.
sql = "INSERT INTO ... VALUES (DEFAULT, 'data1', 'data2');";
sql += "SELECT currval('sequence')";
Prepare and use sql. DEFAULT is the primary key value.
You should get back a result set of the sequence iff the insert
succeeds.
It is still technically 2 queries, but only has the network overhead of
one.
Attachment
Rod,
Very cool indeed, never thought of that
Dave
On Sun, 2003-11-02 at 10:06, Rod Taylor wrote:
> On Sun, 2003-11-02 at 09:28, apnv3@muha.net wrote:
> > Hello.
> >
> > I have a table with one field acting as primary key, the key is
> > retrieved from a sequence-generator in postgresql.
> > Currently I am executing two queries for every insert;
> >
> > Query #1 retrieves a number from a sequence-generator, and places it into the
> > record.
> >
> > Query #2 performs the actual insert.
> >
> > Does anyone have an idea how to cut this down to only one query?
> > And maybe somehow get back the primary key in the ResultSet.
> >
> > Or maybe this is how it is supposed to be done, it is just that I am not
> > very experienced in jdbc/sql to know. :)
>
> If you don't mind it being a little ugly, send both SQL queries in a
> single command.
>
> sql = "INSERT INTO ... VALUES (DEFAULT, 'data1', 'data2');";
> sql += "SELECT currval('sequence')";
>
> Prepare and use sql. DEFAULT is the primary key value.
>
> You should get back a result set of the sequence iff the insert
> succeeds.
>
> It is still technically 2 queries, but only has the network overhead of
> one.
On Sun, 2003-11-02 at 15:28, apnv3@muha.net wrote: > Hello. > > I have a table with one field acting as primary key, the key is > retrieved from a sequence-generator in postgresql. > Currently I am executing two queries for every insert; > > Query #1 retrieves a number from a sequence-generator, and places it into the > record. > > Query #2 performs the actual insert. > > Does anyone have an idea how to cut this down to only one query? > And maybe somehow get back the primary key in the ResultSet. > > Or maybe this is how it is supposed to be done, it is just that I am not > very experienced in jdbc/sql to know. :) JDBC has native support for that with the method Statement.getGeneratedKeys(), only I'm unsure postgresql jdbc driver implements it (actually, querying DataBaseMetaData it says no). I've asked the list some time ago for that information but I got no response :( Bye, Alex
Getting the generated keys out of a postgres database is not trivial, the SERIAL type is really a manufactured type which uses a trigger, and sequences to do it's work. Rod's suggestion of doing an insert and retrieving the current value of the sequence is really the fastest way to get it out. Curval does the right thing ie it will get the value for this connection even if another connection has incremented it before you retrieve it. Dave On Tue, 2003-11-04 at 12:25, Alessandro Polverini wrote: > On Sun, 2003-11-02 at 15:28, apnv3@muha.net wrote: > > Hello. > > > > I have a table with one field acting as primary key, the key is > > retrieved from a sequence-generator in postgresql. > > Currently I am executing two queries for every insert; > > > > Query #1 retrieves a number from a sequence-generator, and places it into the > > record. > > > > Query #2 performs the actual insert. > > > > Does anyone have an idea how to cut this down to only one query? > > And maybe somehow get back the primary key in the ResultSet. > > > > Or maybe this is how it is supposed to be done, it is just that I am not > > very experienced in jdbc/sql to know. :) > > JDBC has native support for that with the method > Statement.getGeneratedKeys(), only I'm unsure postgresql jdbc driver > implements it (actually, querying DataBaseMetaData it says no). > > I've asked the list some time ago for that information but I got no > response :( > > Bye, > Alex > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > >
On Sun, 2003-11-02 at 15:28, apnv3@muha.net wrote: > Hello. > > I have a table with one field acting as primary key, the key is > retrieved from a sequence-generator in postgresql. > Currently I am executing two queries for every insert; > > Query #1 retrieves a number from a sequence-generator, and places it into the > record. > > Query #2 performs the actual insert. > > Does anyone have an idea how to cut this down to only one query? > And maybe somehow get back the primary key in the ResultSet. > > Or maybe this is how it is supposed to be done, it is just that I am not > very experienced in jdbc/sql to know. :) JDBC has native support for that with the method Statement.getGeneratedKeys(), only I'm unsure postgresql jdbc driver implements it (actually, querying DataBaseMetaData it says no). I've asked the list some time ago for that information but I got no response :( Bye, Alex