Lists: | pgsql-general |
---|
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 |
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: | Guillaume Lelarge <guillaume(at)lelarge(dot)info> |
---|---|
To: | Sim Zacks <sim(at)compulab(dot)co(dot)il> |
Cc: | PostgreSQL general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: discard on constraint violation |
Date: | 2011-06-27 12:57:23 |
Message-ID: | 1309179443.14751.15.camel@laptop |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | "David Johnston" <polobo(at)yahoo(dot)com> |
---|---|
To: | "'Sim Zacks'" <sim(at)compulab(dot)co(dot)il>, "'PostgreSQL general'" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: discard on constraint violation |
Date: | 2011-06-27 14:07:36 |
Message-ID: | 005b01cc34d3$91c36fb0$b54a4f10$@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
>>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.