RE: Issues with upserts

[André Hänsel <andre@xxxxxxxx>]

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&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.