Re: batch insertion

On Aug 24, 2013, at 5:15 PM, Korisk <Korisk(at)yandex(dot)ru> wrote:

> Hi!
> I want quick insert into db a lot of data (in form of triplets). Data is formed dynamical so "COPY" is not suitable.

COPY works just fine for dynamically generated data, and it's probably the right thing to use if you're bulk loading data (it's about as fast as you can get for a single threaded load).

Take a look at the PQputCopyData() and PQputCopyEnd() functions.

Cheers,
Steve

> I tried batch insert like this:
>
> insert into triplets values (1,1,1);
> insert into triplets values (1,1,1), (3,2,5), (4,5,5);
> ...
> insert into triplets values (1,1,1), (3,2,5), (4,5,5) .... ;
>
> The more triplets I use the quicker operation is.
> With preparation it looks like this:
>
> res = PQprepare(conn, "qu", "insert into triplets values ($1::bigint, $2::bigint, $3::float);",3, NULL);
> ...
> res = PQprepare(conn, "qu", "insert into triplets values ($1::bigint, $2::bigint, $3::float), ($4::bigint, $5::bigint, $6::float), ($7::bigint, $8::bigint, $9::float), ($10::bigint, $11::bigint, $12::float);",12, NULL);
> ...
>
> The question:
> Is there any way to prepare query with any number of triplets without casting such a long string?
>
> Thank you.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general