Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t (i serial, x text unique)

CREATE TABLE


insert into t(x)
values ('a'), ('b'), ('c')

INSERT 0 3


insert into t (x)
    values ('a'), ('d')
    on conflict (x) do update
    set x = excluded.x || '0'
    returning i as new_i, x as new_x, (select t2.x from t t2 where t2.i = t.i) as old_x


new_i
new_x
old_x


1
a0
a

5
d
null



INSERT 0 2


insert into t (x)
    values ('a0'), ('b')
    on conflict (x) do update
    set x = excluded.x || '0'
    returning i as new_i, x as new_x, (select t2.x from t t2 where t2.i = t.i) as old_x


new_i
new_x
old_x


1
a00
a0

2
b0
b



INSERT 0 2