From: | Sim Zacks <sim(at)compulab(dot)co(dot)il> |
---|---|
To: | PostgreSQL general <pgsql-general(at)postgresql(dot)org> |
Subject: | discard on constraint violation |
Date: | 2011-06-27 12:36:48 |
Message-ID: | 4E087960.2020506@compulab.co.il |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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')"
From | Date | Subject | |
---|---|---|---|
Next Message | Vincent Veyron | 2011-06-27 12:48:20 | Re: An amusing MySQL weakness--not! |
Previous Message | Albe Laurenz | 2011-06-27 11:54:32 | Re: Reusing cached prepared statement slow after 5 executions |