Why postgresql encount duplicate key when key not exists?

Question

When I am inserting data into Postgresql(9.6),throw this error:

ERROR:  duplicate key value violates unique constraint "book_intial_name_isbn_isbn10_key"
DETAIL:  Key (name, isbn, isbn10)=(三銃士, , ) already exists.
SQL state: 23505

I add uniq constraint on columns name, isbn, isbn10.But when I check the distination table,it does not contains the record:

select * from public.book where name like '%三銃%';

How to fix?This is my insert sql:

insert into public.book
select *
from public.book_backup20190405 legacy
where legacy."name" not in
(
    select name
    from public.book
)
limit 1000

Possible duplicate of https://stackoverflow.com/questions/30875185/what-happens-with-duplicates-when-inserting-multiple-rows/30878260 — Erwin Brandstetter, Apr 07 '19 at 23:43

score 2 · Accepted Answer · answered Apr 07 '19 at 14:19

An educated guess, there may be more than one row in the source table book_backup20190405 which has the unique key tuple ('三銃', '', '').

Since the bulk INSERT INTO ... SELECT ... will be be transactional, you'll be none the wiser to the error, since all data will have been rolled back when the constraint fails.

You can verify this by running a dupe check on the source table:

SELECT name, isbn, isbn10, COUNT(*) 
FROM public.book_backup20190405 
WHERE name = '三銃' 
GROUP BY name, isbn, isbn10 
HAVING COUNT(*) > 1;

To see if there are duplicates.

Here's an example of how the source table can be the sole source of duplicates:

http://sqlfiddle.com/#!17/29ba3

Why postgresql encount duplicate key when key not exists?

1 Answers1