CODEWITHSUNDEEP
postgresqluniqueupsert
Richard
Richard

Reputation: 65530

Postgres UPSERT - don't update if all data the same?

I am using Postgres 9.6. I want to update a record if anything about it is different, but keep it the same if not.

This is my SQL, slightly simplified because my table actually has many more fields (run via psycopg2):

query = "INSERT INTO cap(scheme, year, code) "
query += "VALUES(%s, %s, %s) "
query += "ON CONFLICT DO NOTHING"
cursor.execute(query, ('abc', 2015, 'twu'))

However, if I run it twice with exactly the same values, I get two records in the database.

How can I amend this query so that providing the exact same values does not create a new record?

I've tried to read the ON CONFLICT UPDATE documentation but don't know how to adapt it for my case. In particular, it looks to me like DO NOTHING should not insert a row (the docs say: "ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative action"), but it does insert a new row.

Upvotes: 4

Views: 1191

Answers (1)

klin
klin

Reputation: 121594

INSERT ... ON CONFLICT ... needs a unique constraint to catch a conflict, e.g.

create table cap(
    scheme text, 
    year int, 
    code text, 
    unique(scheme, year, code) -- ! added
);

With the unique constraint multiple execution of this query

INSERT INTO cap(scheme, year, code)
VALUES('abc', 2015, 'twu')
ON CONFLICT DO NOTHING

will insert the row only once.

Upvotes: 6

Related Questions