> Note that things will go faster if you do your initial data load using
> "copy from stdin" for the initial bulk data load. individual inserts in
> postgresql are quite costly compared to mysql. It's the transactional
> overhead. by grouping them together you can make things much faster.
> copy from stdin does all the inserts in one big transaction.
You could do "copy from file" as well right? (no performance difference
compared to "copy from stdin") I do this all the time.
Also - maybe I misunderstand something, but why does PostgreSQL's
implementation prohibit it from ignoring insert errors during a copy?
If you added a unique constraint to the table before copying, PostgreSQL
would generate errors due to the unique constraint violation - so I
don't think any additional locking would be required for it to simply
say "If there is an error while copying in, ignore it and continue
inserting other rows"
PostgreSQL's copy command doesn't currently support this, so the temp
table followed by a distinct select is the way to go. But I didn't
follow all of the talk about it requiring locking the table and being
inherently impossible for PostgreSQL to support.
I've wanted a similar feature. I select rows into a table on a regular
basis. I'd like to be able to overlap old values and have PostgreSQL
ignore failed inserts. SQL Server offers a flag that allows you to
ignore inserts whose primary key already exists in the table. The only
solution in PostgreSQL is to run a query to manually delete the
duplicate rows from a temp table before inserting - which takes much
more time.