Re: Catching unique_violation exception on specific column/index - Mailing list pgsql-general

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