I have project where I have to insert a bunch of records periodically (~5k record in 10 minutes periods) from a remote source. I have a staging table which has the same structure as the final target table (no primary key, id is varchar and not unique), to ensure there won't be any duplicates, I'm using the next sql command:
insert into g_his
select *
from tmp_his h
where not exists (select id, times
from g_his g
where g.id = h.id
and g.times = h.times);
After this finished the tmp_his table is truncated.
Is it possible this gets an implicit lock on the g_his table and prevents any other inserts? Or can it be a heavy load? Or any better idea to do this?
DB is Postgres 9.6
Create a unique constraint on g_his(id, times) and use
INSERT INTO g_his
SELECT *
FROM tmp_his h
ON CONFLICT (id, times) DO NOTHING;