Re: insert fail gracefully if primary key already exists

You can user few different solutions:

1. You can perform insert using stored procedure. Inside this procedure You
can use BEGIN...EXCEPTION...END construct - this will handle duplicate key
error. You can find this useful:
http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html
- there
is example of such approach.

2. You can also check if such row exists before insert (also in stored
procedure):
CREATE OR REPLACE FUNCTION "myschema"."InsertIfNotExists" (IN "vKeyValue"
int, IN "vValue1" text, IN "vValue2" text)
RETURNS VOID
AS
$BODY$
BEGIN
IF NOT EXISTS (SELECT 1 FROM "myschema".mytable WHERE "MyKey" =
"vKeyValue") THEN
INSERT INTO "myschema".mytable ("MyKey", "Value1", "Value2")
VALUES ("vKeyValue", "vValue1", "vValue2");

END IF;
END;
$BODY$ LANGUAGE plpgsql

3. You can create trigger before insert which will check if record already
exists:
trigger function could look like this:
CREATE OR REPLACE FUNCTION "myschema"."checkTriggerFunction" ()
RETURNS TRIGGER
AS
$BODY$
BEGIN
IF EXISTS (SELECT 1 FROM "myschema".mytable WHERE "MyKey" = NEW."MyKey")
THEN
RETURN NULL;
ELSE
RETURN NEW;
END IF;
END;
$BODY$
LANGUAGE plpgsql;

and trigger:
CREATE TRIGGER "checkTrigger"
BEFORE INSERT
ON "myschema".mytable
FOR EACH ROW
EXECUTE PROCEDURE "myschema"."checkTriggerFunction"();

Another question is: do You want to update record if primary key exists or
simply ignore insert?
It should be noticed, every check impacts performance.

Regards,
Bartek

2012/2/13 Alessandro Gagliardi <alessandro(at)path(dot)com>

> Sorry I wasn't clear. The application is written in Python. It services
> requests that may go to an external API or to our production database
> (which is Mongo). I should add that we have a somewhat unusual situation in
> which we have two parallel databases: MongoDB for OLTP and newer PostgreSQL
> for OLAP. Because Postgres is only consuming, it would waste precious
> cycles to have the client check to see if a record already exists in
> Postgres before trying to insert it. I'd rather let Postgres deal with that
> (which it does well enough anyway with my primary key constraints). My
> Postgres instance is hosted by Heroku and they provide me with a log which
> is currently being spammed by these "duplicate key value violates unique
> constraint" errors making it impossible to see if there are other errors I
> need to be paying more attention to.
>
>
> On Sat, Feb 11, 2012 at 12:08 AM, Bartosz Dmytrak <bdmytrak(at)eranet(dot)pl>wrote:
>
>> Could You provide more details?
>> PostgreSQL does not allow cross database queries.
>>
>>
>> Regards,
>> Bartek
>>
>>
>> 2012/2/10 Alessandro Gagliardi <alessandro(at)path(dot)com>
>>
>>> I have a situation where I am gradually filling in a table from another
>>> database as the need requires. As it currently is, whenever a record comes
>>> up, it tries to insert it into my database. If the row is already there,
>>> the primary key collides and the insert fails. And that's fine except that
>>> it makes for a really cluttered log (making it hard to find errors that I
>>> really do need to pay attention to). I'm wondering if there's a better
>>> (i.e. cleaner, but possibly also more efficient) way to do this.
>>>
>>> Thoughts?
>>>
>>> Thank you in advance,
>>> -Alessandro
>>>
>>
>>
>