How to catch a particular exception?

Question

It is possible to catch an exception in plpgsql:

EXCEPTION WHEN unique_violation THEN

But how to check for a violation of a particular constraint?

The following example creates two keys: one for a and one for b. How to know which one has caused the exception?

create table t (
  a text unique,
  b text unique
);

create procedure insert_t(a text, b text)
language plpgsql
as $$
begin
  insert into t values (a, b);
  exception when unique_violation then
  raise notice 'is it a or b?';
end
$$;

call insert_t('x', 'foo');
call insert_t('x', 'bar');

select * from t;

Answer 1

In the exception handler, you can get the name of the violated constraint:

DECLARE
   c text;
BEGIN
   BEGIN
      ...
   EXCEPTION WHEN unique_violation THEN
      GET STACKED DIAGNOSTICS c = CONSTRAINT_NAME;
   END;
END;

See the documentation for details.