From: | Gregory Seidman <gss+pg(at)cs(dot)brown(dot)edu> |
---|---|
To: | PostgreSQL general mailing list <pgsql-general(at)postgresql(dot)org> |
Subject: | transactions, serial ids, and JDBC |
Date: | 2002-08-07 22:41:22 |
Message-ID: | 20020807224122.GA10184@cs.brown.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I've come to the point where I really need to run a transaction. In the
past it hasn't been as crucial, so I've been happy with individual queries,
but I am now past that point. I am now trying to insert a row into three
separate tables, and the rows refer to each other. Two of them have SERIAL
ids which need to be used as foreign keys. Here's a trimmed down version of
the tables:
CREATE TABLE A (
id SERIAL not null,
somedata int not null,
primary key (id)
);
CREATE TABLE B (
id SERIAL not null,
moredata int not null,
a_id integer not null REFERENCES A(id),
primary key (id)
);
CREATE TABLE C (
b_id integer not null REFERENCES B(id),
yetmoredata int not null,
primary key (b_id)
);
The transaction needs to look something like this:
BEGIN
INSERT INTO A (somedata) VALUES (1);
INSERT INTO B (moredata, a_id) VALUES (1, <id from last insert>);
INSERT INTO C (yetmoredata, b_id) VALUES (1, <id from last insert>);
END
I don't know how to dependably get the id from the last insert. One
possibility, I suppose, is to call nextval myself and use the value
explicitly, but if there is a way to do it portably (i.e. not depending on
PostgreSQL's specific implementation of a self-incrementing id field) I
would prefer it.
Oh, one more thing. I'm doing this from JDBC. Can I do transactions with a
long text string with all of this, or do I need to send each line
(including BEGIN and END) as a separate Statement? Or is there some better
way?
--Greg
From | Date | Subject | |
---|---|---|---|
Next Message | Neil Conway | 2002-08-07 22:45:37 | Re: transactions, serial ids, and JDBC |
Previous Message | Doug McNaught | 2002-08-07 18:00:39 | Re: Importing blob |