Re: Efficient Insert statement for one record into multiple

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
>