I have a process that runs every 5 minutes and tries to insert a batch of articles into a table. The articles come from web-scrapping, so there are cases in which I am trying to insert a batch that contains articles which have already been saved into the DB.
My primary key is
You could insert using the
WHERE NOT EXISTS clause.
For example, consider a
test table with a numeric
id as primary key and a textual
db=> CREATE TABLE test(id BIGSERIAL PRIMARY KEY, name TEXT); CREATE TABLE -- Insertion will work - empty table db=> INSERT INTO test(id, name) SELECT 1, 'Partner number 1' WHERE NOT EXISTS (SELECT 1,2 FROM test WHERE id=1); INSERT 0 1 -- Insertion will NOT work - duplicate id db=> INSERT INTO test(id, name) SELECT 1, 'Partner number 1' WHERE NOT EXISTS (SELECT 1,2 FROM test WHERE id=1); INSERT 0 0 -- After two insertions, the table contains only one row db=> SELECT * FROM test; id | name ----+------------------ 1 | Partner number 1 (1 row)
Quoting the documentation:
ON CONFLICTcan be used to specify an alternative action to raising a unique constraint or exclusion constraint violation error.
The action can be
DO NOTHING, or a
DO UPDATE. The second approach is often referred to as Upsert - a portmanteau of Insert and Update.
WHERE NOT EXISTS is equivalent to
ON CONFILCT DO NOTHING. See the query plans for a deeper dive.