Re: bulk inserts

On 2009-09-29, Alan Hodgson <ahodgson(at)simkin(dot)ca> wrote:
> On Tuesday 29 September 2009, Sam Mason <sam(at)samason(dot)me(dot)uk> wrote:
>> ?? I'm not sure what you're implying about the semantics here, but it
>> doesn't seem right. COPY doesn't somehow break out of ACID semantics,
>> it's only an *optimization* that allows you to get large quantities of
>> data into the database faster. The main reason it's faster is because
>> parsing CSV data is easier than parsing SQL.
>>
>> At least I think that's the only difference; anybody know better?
>
> I think a big reason is also that the client can stream the data without
> waiting for a network round trip ack on every statement.

a single insert statement can insert many rows.

the win with copy is more that the data part can be parsed one record
at a time. whereas for the insert the whole must be parsed, also the
quoting rules are simpler for CSV or pg's tab-delimted format.