Skip site navigation (1) Skip section navigation (2)

Re: Efficient Insert statement for one record into multiple

From: Dave Cramer <pg(at)fastcrypt(dot)com>
To: Albe Laurenz <all(at)adv(dot)magwien(dot)gv(dot)at>
Cc: "Roland Walter *EXTERN*" <roland(dot)walter(dot)rwa(at)gmx(dot)net>, "Peter Neu" <peter(dot)neu(at)gmx(dot)net>, <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: Efficient Insert statement for one record into multiple
Date: 2006-12-18 14:50:13
Message-ID: 3D7E9990-2CC9-4129-8732-69DDAA6817CD@fastcrypt.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
On 18-Dec-06, at 9:36 AM, Albe Laurenz wrote:

>>> I have this task that I need to insert one user record into to  
>>> tables
> A
>>> and B.
>>>
>>> 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.
>>
>> 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.
>
> This is _bad_.
No, this works
>
> What if somebody did a nextval("primary_A") between the insert into A
> and the insert into B?
as long as you use currval then it works as advertised
>
> If there is a sequence "primary_A" for table A, you should proceed
> as follows:
This requires two trips to the db. Roland's solution doesn't, and it  
does work.
>
> - select nextval("primary_A")
> - then insert into A with this primary key
> - then insert into B with the same key
>
> That should all be in a single transaction!
This doesn't even have to be in a single transaction.

Dave
>
> Yours,
> Laurenz Albe
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>


In response to

pgsql-jdbc by date

Next:From: Albe LaurenzDate: 2006-12-18 15:06:03
Subject: Re: Efficient Insert statement for one record into multiple
Previous:From: Albe LaurenzDate: 2006-12-18 14:36:50
Subject: Re: Efficient Insert statement for one record into multiple

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group