java - JDBC - PostgreSQL - 批量插入+唯一索引

Question

我有一个对某些字段具有唯一约束的表。我需要在这个表中插入大量记录。为了加快速度，我使用 JDBC 进行批量更新(驱动程序版本为 8.3-603)。有没有办法做到以下几点:

• 每个批处理执行时，我都需要将该批处理中不违反唯一索引的所有记录写入表中；

• 每个批处理执行时我都需要接收该批处理中未插入数据库的记录，这样我就可以保存“错误”的记录

？

Answer 1

最有效的方法是这样的:

• 创建一个与目标表结构相同但没有唯一约束的临时表
• 将所有行批量插入到该临时表中。最有效的方法是使用copy或使用CopyManager (尽管我不知道您的旧驱动程序版本是否已支持这一点。

完成后，将有效行复制到目标表中:

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

请注意，以上不是并发安全!如果其他进程执行相同的操作，您可能仍然会遇到唯一的 key 违规。为了防止这种情况，您需要锁定目标表。

如果您想删除复制的行，您可以使用可写 CTE 来实现:

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

staging_table.id 上的(非唯一)索引应该有助于提高性能。