RE: Issues with upserts

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.