Thread: Sequence ID from INSERT

Sequence ID from INSERT

From

Daryl Beattie

Date:

23 July 2002, 14:06:18

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.

Re: Sequence ID from INSERT

From

Dave Cramer

Date:

23 July 2002, 14:18:24

Daryl,

your second example where you get the id first is the only way I know
how to do it

BTW it doesn't have to be done in a transaction. the id will not be used
by another connection.

DAve
On Tue, 2002-07-23 at 11:05, Daryl Beattie wrote:
> 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.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>

Re: Sequence ID from INSERT

From

"Thomas De Vos"

Date:

24 July 2002, 16:28:07

you can create your table as follows:

CREATE SEQUENCE Food_Id_seq START 1;
CREATE TABLE Food (
     Id     SERIAL PRIMARY KEY ,
     Name    VARCHAR(10)
 );
 alter table Food ALTER ID SET DEFAULT NEXTVAL('Food_Id_seq');

when you do an insert, the ID will be set to nextVal Food_Id_Seq


insert into food (NAME) values ('apple');

-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer
Sent: 23 July 2002 16:17
To: Daryl Beattie
Cc: 'pgsql-jdbc@postgresql.org'
Subject: Re: [JDBC] Sequence ID from INSERT


Daryl,

your second example where you get the id first is the only way I know
how to do it

BTW it doesn't have to be done in a transaction. the id will not be used
by another connection.

DAve
On Tue, 2002-07-23 at 11:05, Daryl Beattie wrote:
> 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.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>




---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

__________________________________________________
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.comm