Обсуждение: 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.

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