On Sat, Aug 24, 2013 at 7:15 PM, Korisk <kor...@yandex.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. > 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?
yes. you can use COPY with some tricks, or use arrays. CREATE TYPE triplet_t AS (a bigint, b bigint, c bigint); WITH data AS (SELECT UNNEST($1::triplet_t[]) INSERT INTO triplets SELECT * FROM data; Also see libpqtypes: http://libpqtypes.esilo.com/man3/pqt-composites.html merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
