Re: Catching unique_violation exception on specific column/index

Alexey Dokuchaev schrieb am 11.06.2018 um 12:10:
> 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

What's wrong with:

INSERT ...
ON CONFLICT (foo_key) DO NOTHING