On 10/18/06, Ilja Golshtein <ilejn@xxxxxxxxx> 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
