postgresqlsql-insertselect-insert

Insert 10 records that don't conflict

If I want to insert 10 records from table_a to table_b I can do this:

insert into table_b
select * from table_a
limit 10

Now lets set I want to insert 10 records from table_a to table_b that don't conflict how do I do it?

If I do this:

insert into table_b
select * from table_a
limit 10
on conflict do nothing

Then 10 records won't be inserted. If any of those first 10 records conflict then it wont keep going to fully insert 10 records. It will be 10 minus conflicts which could be 0.

How do I insert 10 non-conflicting records?

Solution

  • Insert from a query that makes sure the records do not exist in table_b:

    insert into table_b
select * from table_a
except
select * from table_b
limit 10