discard on constraint violation

Поиск
Список
Период
Сортировка
От Sim Zacks
Тема discard on constraint violation
Дата
Msg-id 4E087960.2020506@compulab.co.il
обсуждение исходный текст
Ответы Re: discard on constraint violation  (Guillaume Lelarge <guillaume@lelarge.info>)
Re: discard on constraint violation  ("David Johnston" <polobo@yahoo.com>)
Список pgsql-general
Is there a way to tell a table to discard an insert if it violates a
constraint (unique in my case) instead of giving an error? I don't want
the overhead of a trigger on each row inserted.


My situation is that I'm reading data from an external website with lots
of duplicate data. I am reading the data in a plpythonu function. I
never update, only insert. A trigger on the table that has to check if
each row exists before trying the insert is too much overhead.

I tried in plpython:


create or replace function testme() returns bool as
$$
try:
     plpy.execute("insert into
reports.survey_types(typeid,name)values(1,'test')")

     return true

except:

     plpy.notice('the constraint was violated')
     return false
$$language 'plpythonu';

where the insert violates a unique constraint and it never gets to the
except. The function dies and doesn't get to my except with:

WARNING:  plpython: in function testme:
DETAIL: <class 'plpy.SPIError'>: Unknown error in PLy_spi_execute_query


ERROR:  duplicate key violates unique constraint "survey_types_pkey"
CONTEXT:  SQL statement "insert into
reports.survey_types(typeid,name)values(1,'test')"


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

Предыдущее
От: "Albe Laurenz"
Дата:
Сообщение: Re: Reusing cached prepared statement slow after 5 executions
Следующее
От: Vincent Veyron
Дата:
Сообщение: Re: An amusing MySQL weakness--not!