Thread: Efficient Insert statement for one record into multiple tables?
Hello,
I have this task that I need to insert one user record into to tables A and B.
User table A contains primary key and main data. Table B contains extra data for
statistics. Table A has one field Id which is a sequence so it gets a unique value
for each new insert. I would like to insert this value into the other table B as well as
foreign key.
So I’d like to do something like this
insert into A (user_name, value2 ) values ( “foo”, “foo2”)
AND
insert into B ( id_from_a , statistic_data) values ( 23, “bla”)
How is this done the best way? I would like to do this in one call.
Sorry, for his dumb question but I’m not very experienced in Postgresql. :o(
Cheers,
Pete
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Peter Neu wrote:
> Hello,
>
> I have this task that I need to insert one user record into to tables A
> and B.
>
> User table A contains primary key and main data. Table B contains extra
> data for
>
> statistics. Table A has one field Id which is a sequence so it gets a
> unique value
>
> for each new insert. I would like to insert this value into the other
> table B as well as
>
> foreign key.
>
> So I’d like to do something like this
>
> insert into A (user_name, value2 ) values ( “foo”, “foo2”)
>
> AND
>
> insert into B ( id_from_a , statistic_data) values ( 23, “bla”)
>
> How is this done the best way? I would like to do this in one call.
>
>
>
> Sorry, for his dumb question but I’m not very experienced in Postgresql. :o(
>
If you know the name of the sequence for the primary key in table A, say
"primary_A", then use the following to insert into table B direct after
you inserted into table A in the same connection:
insert into B (id_from_a, statistic_data) values( currval("primary_A"),
bla")
This works only if your insertion into table A called in some form
nextval("primary_A"), may it be as default value or in the
insertion-statement itself.
Regards,
Roland.
- --
Dipl.-Phys. Roland Walter
mailto: roland (dot) walter (dot) rwa (at) gmx (dot) net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org
iD8DBQFFhntJxoOEAkary1ERAljXAJ9DASPP58I74Cj6Nt59rDzaIGlHjQCdFZRr
lFQGZaGY24/Sj77M6Le9xbc=
=Yr/X
-----END PGP SIGNATURE-----
Hi,
in my create table statement I got this line:
id int4 DEFAULT nextval('public.user_auth_id2_seq'::text)
So according to your suggestions this would be
insert into B (id_from_a, statistic_data) values(
currval("public.user_auth_id2_seq"),bla")
,right?
If I got you right I cannot link this two insert statements together.
So I need two times to open a prepared statement and execute it.
Would I need to do this in a transaction or is it safe to do this
without it?
Cheers,
Pete
On 18-Dec-06, at 7:05 AM, Peter Neu wrote:
> Hi,
>
> in my create table statement I got this line:
>
> id int4 DEFAULT nextval('public.user_auth_id2_seq'::text)
>
> So according to your suggestions this would be
>
> insert into B (id_from_a, statistic_data) values(
> currval("public.user_auth_id2_seq"),bla")
>
> ,right?
>
> If I got you right I cannot link this two insert statements together.
> So I need two times to open a prepared statement and execute it.
> Would I need to do this in a transaction or is it safe to do this
> without it?
It's safe to do it without a transaction, what you have to make sure
is that you do it with the same connection object.
Dave
>
> Cheers,
> Pete
>
>
>
>
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that
> your
> message can get through to the mailing list cleanly
>