Re: COPY FROM STDIN instead of INSERT

On 10/18/06, Ilja Golshtein <ilejn(at)yandex(dot)ru> wrote:
> I've tried to play with batches and with peculiar constructions
> like INSERT (SELECT .. UNION ALL SELECT ..) to improve performance, but not satisfied with the result I've got.

postgresql 8.2 (beta) supports the 'multiple insert' syntax, so you
can insert multiple rows in an insert statement without using 'union
all'. it's pretty fast, although not as fast as copy. btw, if you
have a lot of indexes on your table, the input method is not so
important.

aside: new insert syntax has one very nice side effect. assuming the
table(s) are already defined, I can transfer data from mysql to
postgresql via:

mysqldump --compatible=postgresql esilo | grep INSERT | psql

without any extra processing for most cases. gotta give some points
to mysql for adding postgresql compatibility which sorta works.

merlin