Skip to content

Instantly share code, notes, and snippets.

@Logioniz
Created December 16, 2016 12:42
Show Gist options
  • Save Logioniz/c781671d80acb9d6b512e4b9d9ea6e32 to your computer and use it in GitHub Desktop.
Save Logioniz/c781671d80acb9d6b512e4b9d9ea6e32 to your computer and use it in GitHub Desktop.
Postgresql upsert with many constraints
create table test (id int primary key, a int, b int, unique (a, b));
\d+ test
begin;
insert into test values (1, 1, 1);
do $$
declare
_cn text;
begin
insert into test values (1, 2, 2);
exception when others then
get stacked diagnostics _cn = constraint_name;
case
when _cn = 'test_pkey' then
update test set a = 2, b = 2 where id = 1;
when _cn = 'test_a_b_key' then
update test set id = 3 where a = 2 and b = 2;
end case;
end;
$$ language plpgsql;
select * from test;
do $$
declare
_cn text;
begin
insert into test values (3, 2, 2);
exception when others then
get stacked diagnostics _cn = constraint_name;
case
when _cn = 'test_pkey' then
update test set a = 2, b = 2 where id = 1;
when _cn = 'test_a_b_key' then
update test set id = 3 where a = 2 and b = 2;
end case;
end;
$$ language plpgsql;
select * from test;
commit;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment