java - JDBC - PostgreSQL - batch insert + unique index -

i have table unique constraint on field. need insert large number of records in table. make faster i'm using batch update jdbc (driver version 8.3-603). there way following:

• every batch execute need write table records batch don't violate unique index;

• every batch execute need receive records batch not inserted db, save "wrong" records

?

the efficient way of doing this:

• create staging table same structure target table without unique constraint
• batch insert rows staging table. efficient way use copy or use copymanager (although don't know if supported in ancient driver version.

once done copy valid rows target table:

insert target_table(id, col_1, col_2) select id, col_1, col_2 staging_table  not exists (select *                   target_table                    target_table.id = staging_table.id); 

note above not concurrency safe! if other processes same thing might still unique key violations. prevent need lock target table.

if want remove copied rows, using writeable cte:

with inserted (     insert target_table(id, col_1, col_2)     select id, col_1, col_2     staging_table      not exists (select *                       target_table                        target_table.id = staging_table.id)     returning staging_table.id; )  delete staging_table id in (select id inserted); 

a (non-unique) index on staging_table.id should performance.