How to return the old value of a column in a postgres INSERT ON CONFLICT DO UPDATE?

Question

I am attempting to run an "upsert" in postgres like:

INSERT INTO my_table (
    id, -- unique key
    name,
    hash
) VALUES (
    '4b544dea-b355-463c-8fba-40c36ac7cb0c',
    'example',
    '0x0123456789'
) ON CONFLICT (
    id
) DO UPDATE SET
    name = 'example',
    hash = '0x0123456789'
RETURNING
    OLD.hash;

I would like to return the previous value of the hash column (the example above is not a valid query due to OLD not being a valid table alias). Importantly, I am trying to find a method that does this in a way that won't cause conflicts under load. Is this even possible? Or is the only solution to do a read-before-write in a transaction?

Answer 1

I did end up finding a workaround, though it doesn't strictly guarantee atomicity, but in general could be a good strategy depending on your use case.

INSERT INTO my_table (
    id, -- unique key
    name,
    hash
) VALUES (
    '4b544dea-b355-463c-8fba-40c36ac7cb0c',
    'example',
    '0x0123456789'
) ON CONFLICT (
    id
) DO UPDATE SET
    name = 'example',
    hash = '0x0123456789'
RETURNING
    name,
    hash, -- will be the new hash
    (SELECT hash FROM my_table WHERE my_table.id = '4b544dea-b355-463c-8fba-40c36ac7cb0c') -- will be the old hash
    ;

Answer 2

Another possible answer, if you are willing to update the schema, would be to store the previous value in another column:

ALTER table my_table add column prev_hash text;

INSERT INTO my_table (
    id, -- unique key
    hash
) VALUES (
    1,
    'a'
) ON CONFLICT (
    id
) DO UPDATE SET
    hash = excluded.hash,
    prev_hash = my_table.hash
RETURNING
    id,
    hash,      -- will be the new hash
    prev_hash  -- will be the old hash