12

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?

3 Answers 3

11

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
Sign up to request clarification or add additional context in comments.

Comments

4

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
    ;

1 Comment

If there is an insert (meaning ON CONFLICT was not triggered), the RETURNING block will fail because the record didn't previously exist.
0

You can use CTEs.

If you need exact one column and one row, below is simpler approach.

with prev as (
    select name
    from student
    where id=$1
)
insert into student(id, name)
values($1, $2)
on conflict (id) do update
    set name=excluded.name
returning (select name from prev);

For your need, can use 2 CTEs:

with prev as (
    select *
    from student
    where id=$1
), upd as (
  insert into student(id, name)
    values($1, $2)
    on conflict (id) do update
        set name=excluded.name
)
select * from prev;

Examples

-- Bootstrap script to create tables and insert some data
create table student (
  id serial primary key,
  name text
);

insert into student(name)
values ('st1'), ('st2');

-- returns 'st1'
with prev as (
    select name
    from student
    where id=1
)
insert into student(id, name)
values(1, 'st1-altered')
on conflict (id) do update
    set name=excluded.name
returning (select name from prev);

-- returns id: 2, name: 'st2'
with prev as (
    select *
    from student
    where id=2
), upd as (
  insert into student(id, name)
    values(2, 'st2-changed')
    on conflict (id) do update
        set name=excluded.name
)
select * from prev;

-- returns nil (since there's no previous value)
with prev as (
    select *
    from student
    where id=3
), upd as (
  insert into student(id, name)
    values(3, 'st3')
    on conflict (id) do update
        set name=excluded.name
)
select * from prev;
  

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.