Sequence ID from INSERT

From: Daryl Beattie <dbeattie(at)insystems(dot)com>
To: "'pgsql-jdbc(at)postgresql(dot)org'" <pgsql-jdbc(at)postgresql(dot)org>
Subject: Sequence ID from INSERT
Date: 2002-07-23 15:05:51
Message-ID: 4160E6FC08ABD21191F000805F857E9304DF455E@mail.insystems.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Dear Postgresql-JDBCers,

Let's say I have created a table like this:

CREATE TABLE Food (
Id SERIAL PRIMARY KEY,
Name VARCHAR(10)
);

What is the best way to use JDBC to insert a row into the database
and get the Sequence Id right out of it? I don't want to start a transaction
and have to do another query that looks like this:

BEGIN;
INSERT INTO Food (Name) VALUES ('Apple');
SELECT currval('Food_Id_seq');
COMMIT;

Or ones like these:

BEGIN;
new_id = output of "SELECT nextval('Food_Id_seq')";
INSERT INTO Food (Id, Name) VALUES (new_id, 'Apple');
COMMIT;

BEGIN;
INSERT INTO Food (Name) VALUES ('Apple');
SELECT MAX(Id) FROM Food; -- will check ResultSet of this separate query.
COMMIT;

I want to do it in one statement. Normally I would
setAutoCommit(false), execute two separate statements, and then commit().
However, I was wondering if there's a way to do all this in a ... "cleaner"
fashion? Can anybody provide me with a sample?

Thanks,

Daryl.

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Dave Cramer 2002-07-23 15:16:45 Re: Sequence ID from INSERT
Previous Message Andrew Sullivan 2002-07-23 14:45:41 Re: Postgres JDBC