2

After reading at How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL? I'm curious which approach is better (and faster) for UPSERT.

I am currently writing an application where in most cases data only updates (new rows are rare) therefore I think will be better use this approach without trying to insert first (from Postgres DOCS):

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- Do nothing, and loop to try the UPDATE again.
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

Or will be better use INSERT ... ON CONFLICT DO UPDATE?

Tim Sazon
  • 41
  • 2
  • 7
  • 2
    Definitely `insert on conflict`. [Right from the docs you linked to](https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE): "*It is recommended that applications use INSERT with ON CONFLICT DO UPDATE rather than actually using this pattern*" –  May 30 '18 at 08:08
  • Thank you. I just thought that insert-update will be slower than update-insert in my case. – Tim Sazon May 30 '18 at 08:39
  • 3
    I once did some performance tests and `insert on conflict` was just as fast as an `update` (at least in my environment I could not measure a difference) –  May 30 '18 at 08:42

0 Answers0