I'm using Python to write to a postgres database:
sql_string = "INSERT INTO hundred (name,name_slug,status) VALUES (" sql_string += hundred + ", '" + hundred_slug + "', " + status + ");" cursor.execute(sql_string)
But because some of my rows are identical, I get the following error:
psycopg2.IntegrityError: duplicate key value violates unique constraint "hundred_pkey"
How can I write an 'INSERT unless this row already exists' SQL statement?
I've seen complex statements like this recommended:
IF EXISTS (SELECT * FROM invoices WHERE invoiceid = '12345') UPDATE invoices SET billed = 'TRUE' WHERE invoiceid = '12345' ELSE INSERT INTO invoices (invoiceid, billed) VALUES ('12345', 'TRUE') END IF
But firstly, is this overkill for what I need, and secondly, how can I execute one of those as a simple string?
INSERT ... ON CONFLICT DO NOTHING/UPDATE
It solves many of the subtle problems you can run into when using concurrent operation, which some other answers propose.