discard on constraint violation

Started by Sim Zacksalmost 15 years ago3 messagesgeneral
Jump to latest
#1Sim Zacks
sim@compulab.co.il

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')"

#2Guillaume Lelarge
guillaume@lelarge.info
In reply to: Sim Zacks (#1)
Re: discard on constraint violation

On Mon, 2011-06-27 at 15:36 +0300, Sim Zacks wrote:

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.

Without a trigger, you can't.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

#3David G. Johnston
david.g.johnston@gmail.com
In reply to: Sim Zacks (#1)
Re: discard on constraint violation

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.

Create a staging table into which you import your external data and then
write a query of the form "INSERT INTO realtable SELECT FROM stagingtable
WHERE stagingtable NOT EXISTS realtable".

David J.