Jeremy Smith wrote:
It sounds like you aren't adding a WHERE clause to prevent the duplicate rows from being updated. It would help if you could share your query, but in general this could look like this: INSERT INTO my_table (col1, col2) SELECT col1, col2 FROM other_table ON CONFLICT (col1, col2) DO UPDATE SET (col1, col2) = (EXCLUDED.col1, EXCLUDED.col2) WHERE (my_table.col1, my_table.col2) IS DISTINCT FROM (EXCLUDED.col1, EXCLUDED.col2) Here’s an example: https://dbfiddle.uk/?rdbms=postgres_14 <https://dbfiddle.uk/?rdbms=postgres_14&fiddle=b48d062d2eedbab14157359694b16081> &fiddle=b48d062d2eedbab14157359694b16081 CREATE TABLE t ( id serial PRIMARY KEY, name text NOT NULL UNIQUE, address text NOT NULL ); INSERT INTO t(name, address) VALUES ('foo', 'Baker street'),('bar', 'Miller street') ON CONFLICT (name) DO UPDATE SET address = EXCLUDED.address WHERE (t.name, t.address) IS DISTINCT FROM (EXCLUDED.name, EXCLUDED.address); INSERT INTO t(name, address) VALUES ('foo', 'Baker street'),('bar', 'Miller street') ON CONFLICT (name) DO UPDATE SET address = EXCLUDED.address WHERE (t.name, t.address) IS DISTINCT FROM (EXCLUDED.name, EXCLUDED.address); INSERT INTO t(name, address) VALUES ('foo', 'Baker street'),('bar', 'Miller street') ON CONFLICT (name) DO UPDATE SET address = EXCLUDED.address WHERE (t.name, t.address) IS DISTINCT FROM (EXCLUDED.name, EXCLUDED.address); INSERT INTO t(name, address) VALUES ('foo', 'Baker street'),('bar', 'Miller street') ON CONFLICT (name) DO UPDATE SET address = EXCLUDED.address WHERE (t.name, t.address) IS DISTINCT FROM (EXCLUDED.name, EXCLUDED.address); SELECT last_value FROM t_id_seq; This will yield “8”, showing that new sequence numbers have been generated for each attempt.
