Catching unique_violation exception on specific column/index

Поиск
Список
Период
Сортировка
От Alexey Dokuchaev
Тема Catching unique_violation exception on specific column/index
Дата
Msg-id 20180611101033.GA40332@regency.nsu.ru
обсуждение исходный текст
Ответы Re: Catching unique_violation exception on specific column/index
Re: Catching unique_violation exception on specific column/index
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: Nicolas Seinlet
Дата:
Сообщение: Re: Sort is generating rows
Следующее
От: Thomas Kellerer
Дата:
Сообщение: Re: Catching unique_violation exception on specific column/index