Re: automatic sequence number?

On Sun, 2003-11-02 at 09:28, apnv3(at)muha(dot)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.