Trap specific named unique constraint exception

Question

I'd like to trap an exception in a plpgsql function if it violates a particular named unique constraint, and for all other cases to throw the original error to the client, including for other unique constraints (that match a different name).

I can see in the manual how to do it for all unique constraint violations using EXCEPTION WHEN unique_violation THEN, but how can I do it only for a specific one?

Answer 1

You can get the constraint name via GET STACKED DIAGNOSTICS in the exception handler:

CREATE TABLE t (id INTEGER PRIMARY KEY);

DO $$
DECLARE
  n TEXT;
  c TEXT := 'my_constraint_name';
BEGIN
  INSERT INTO t VALUES (1), (1);
EXCEPTION
  WHEN UNIQUE_VIOLATION THEN
    GET STACKED DIAGNOSTICS n := CONSTRAINT_NAME;
    IF n = c THEN
      -- do whatever, your constraint raised this
    ELSE
      RAISE;
    END IF;
END $$