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.
