Thread: Help with INSERT into 2 tables
I am new to SQL and don't know how to INSERT records to 2 tables.
The first table:
CREATE TABLE aaaa ( aaaaid SERIAL PRIMARY KEY, text VARCHAR(20) );
Second table:
CREATE TABLE bbbb ( bbbbid SERIAL PRIMARY KEY, aaaaid INTEGER REFERENCES aaaa (aaaaid) ON
DELETE CASCADE, sometext VARCHAR(200) );
I want to insert related records to both table. The problem is that
in order to insert record to the second table it's necessary to know
"aaaaid" field from the first table("text" is not UNIQUE):
INSERT INTO aaaa (text) VALUES ('Some info');
INSERT INTO bbbb (aaaaid, sometext) VALUES (?????, 'Some text');
How is it possible to do that?
(I am inserting this from JAVA).
Thanks for help.
Gintaras
You will need to:
1. Insert into aaaa
2. Get value of aaaaid for inserted record
3. Include aaaaid value explicitly in your insert to
bbbb.
The only question is how to do 2 above. AFAIK doing a
separate command "SELECT
currval(aaaaid_sequence_name)" should work.
Alternatively, if the oid of the last inserted record
is returned to your Java program (sorry, I don't know
anything about the JDBC driver functionality, so don't
know whether that is so or not), then you can do
"SELECT aaaaid FROM aaaa WHERE oid = <oid of inserted
record>": which would probably be safer.
--- Gintas <gntrs@hotmail.com> wrote:
> I am new to SQL and don't know how to INSERT records
> to 2 tables.
>
> The first table:
>
> CREATE TABLE aaaa ( aaaaid SERIAL PRIMARY KEY,
> text VARCHAR(20)
> );
>
> Second table:
>
> CREATE TABLE bbbb ( bbbbid SERIAL PRIMARY KEY,
> aaaaid INTEGER REFERENCES
> aaaa (aaaaid) ON
> DELETE CASCADE,
> sometext VARCHAR(200)
> );
>
> I want to insert related records to both table. The
> problem is that
> in order to insert record to the second table it's
> necessary to know
> "aaaaid" field from the first table("text" is not
> UNIQUE):
>
> INSERT INTO aaaa (text) VALUES ('Some info');
> INSERT INTO bbbb (aaaaid, sometext) VALUES (?????,
> 'Some text');
>
> How is it possible to do that?
> (I am inserting this from JAVA).
>
> Thanks for help.
>
>
>
> Gintaras
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org
__________________________________________________
Do You Yahoo!?
Find the one for you at Yahoo! Personals
http://personals.yahoo.com
On 13 Nov 2001, Gintas wrote:
> I am new to SQL and don't know how to INSERT records to 2 tables.
>
> The first table:
>
> CREATE TABLE aaaa ( aaaaid SERIAL PRIMARY KEY,
> text VARCHAR(20)
> );
>
> Second table:
>
> CREATE TABLE bbbb ( bbbbid SERIAL PRIMARY KEY,
> aaaaid INTEGER REFERENCES aaaa (aaaaid) ON
> DELETE CASCADE,
> sometext VARCHAR(200)
> );
>
> I want to insert related records to both table. The problem is that
> in order to insert record to the second table it's necessary to know
> "aaaaid" field from the first table("text" is not UNIQUE):
>
> INSERT INTO aaaa (text) VALUES ('Some info');
> INSERT INTO bbbb (aaaaid, sometext) VALUES (?????, 'Some text');
I think you want:currval('aaaa_aaaaid_seq')
>>>>> "Gintas" == Gintas <gntrs@hotmail.com> writes:
Gintas> I am new to SQL and don't know how to INSERT records to 2 tables. Gintas> The first table:
Gintas> CREATE TABLE aaaa ( aaaaid SERIAL PRIMARY KEY, Gintas> text VARCHAR(20)
Gintas> );
Gintas> Second table:
Gintas> CREATE TABLE bbbb ( bbbbid SERIAL PRIMARY KEY, Gintas> aaaaid INTEGER
REFERENCESaaaa (aaaaid) ON Gintas> DELETE CASCADE, Gintas> sometext VARCHAR(200) Gintas>
);
Gintas> I want to insert related records to both table. The Gintas> problem is that in order to insert record to
thesecond Gintas> table it's necessary to know "aaaaid" field from the first Gintas> table("text" is not UNIQUE):
Gintas> INSERT INTO aaaa (text) VALUES ('Some info'); Gintas> INSERT INTO bbbb (aaaaid, sometext) VALUES (?????,
'Sometext');
Gintas> How is it possible to do that? (I am inserting this from Gintas> JAVA).
The normal way to do this is to explicitly pull out the serial value
from the underlying sequence. I.e., something like this
select nextval('aaaa_aaaaid_seq'); begin transaction; insert into aaaa (aaaaid, text) values ($seqno, 'Some
info'); insert into bbbb (aaaaid, sometext) VALUES ($seqno, 'Some text'); end transaction;
where you use the jdbc calls to retrieve the result from the "select
nextval...".
SERIAL types are not quite as useful as people tend to think unless
you have the ability to identify the value from some other
characteristic. If the ID is purely an ID and you don't care about
its value, you still have to have a way of *finding* it.
roland
-- PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
roland@astrofoto.org Forest Hills, NY 11375
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
CREATE TABLE a (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE b (id SERIAL PRIMARY KEY, key_a INTEGER REFERENCES a (id) ON DELETE CASCADE,data TEXT);
> I want to insert related records to both table. The problem is that
> in order to insert record to the second table it's necessary to know
> a.id
Common question, and there's several possible answers. I'm going to break
slightly from the norm though. Postgres has some cool features, and since we
can stuff this logic into the database, we might just as well. So let's play
with them!
CREATE VIEW ab AS SELECT a.id, a.name, b.data FROM a,b WHERE a.id = b.key_a;
CREATE FUNCTION ab_insert (text, text) RETURNS INTEGER AS 'DECLARE newid INTEGER; newname ALIAS FOR $1; newdata
ALIASFOR $2;BEGIN newid := nextval(''a_id_seq''); RAISE NOTICE ''newid is %'', newid; INSERT INTO a (id, name)
VALUES(newid, newname); INSERT INTO b (key_a, data) VALUES (newid, newdata); RETURN newid;END;' LANGUAGE
'plpgsql';
CREATE RULE ab_ins_rule AS ON INSERT TO ab DO INSTEAD SELECT ab_insert(new.name, new.data);
I'd really like to figure out how to write a function that doesn't return
anything (a "procedure" for all you pedantic CS types). Oh well.
- --
Andrew G. Hammond mailto:drew@xyzzy.dhs.org http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481
5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iEYEARECAAYFAjv0roYACgkQCT73CrRXhLFd4wCeNvUf1sYztKvs0Xqq9cfcDy97
n/wAmwXdCCaxrKQ6oTbtqSyhJ2IhSExG
=78uf
-----END PGP SIGNATURE-----
Andrew, > I'd really like to figure out how to write a function that doesn't > return > anything (a "procedure" for all you pedantic CS types). Oh well. Easy. Try: CREATE FUNCTION ... RETURNS OPAQUE AS ' All triggers are expected to return Opaque, that is, nothing. FYI, A "procedure" in SQL92 terms is waht we in Postgres call a Function, except that Stored Procedures can return one or more result sets as well/instead of a value. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco