Hi there,
I have a table with several UNIQUE and CHECK constraints. One of these
UNIQUE constraints actually *can* be violated -- not on the table level,
of course, but on the application level -- meaning, if the entry with
particular foo_key is already in there, do not throw an exception, just
silently do nothing.
The usual approach ("EXCEPTION WHEN unique_violation THEN ... END") does
not really cut it because I want to catch unique_violation only when it
happens on "foo_key", and still rightfully complain on others. However,
there is no "EXCEPTION WHEN unique_violation ON (foo_key)" or something.
Is there a way to do this without using triggers and in a less ugly way
than the code below?
IF SQLERRM = 'duplicate key value violates unique constraint' ||
' "foo_key"' THEN
RAISE NOTICE '%: %', SQLSTATE, SQLERRM;
ELSE
RAISE EXCEPTION '%: %', SQLSTATE, SQLERRM;
END IF;
./danfe