Re: insert fail gracefully if primary key already exists

First, to answer your question, in this case, I do not expect the data to
be different from what is already stored, so I should be able to safely
ignore the insert if the primary key already exists. (Though an answer to
the alternative "UPSERT" question would also be welcome as that may prove
to be an issue as well.)

I tried to do something like
http://www.postgresql.org/docs/9.0/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPINGonly
like this:

BEGIN
INSERT INTO blocks (block_id, user_id, created, shared, private,
moment_type) VALUES (E'4f3adf9cfa63b31c77000b7c',
E'4ee02f599c6c3f6d360076ce', '2012-02-14T22:26:36.721980'::timestamp,
false, false, E'ambient')
EXCEPTION
WHEN unique_violation THEN
NULL;
END;

But I get:

ERROR: syntax error at or near "INSERT"
SQL state: 42601
Character: 11

I feel like I must be doing something stupid here, but I can't see what it
is.

At the same time, I was also thinking using a trigger might be preferable
so I don't have to mess with application code. But I'm a little mystified
by how your example would work. The checkTriggerFunction makes reference to
NEW."MyKey" and yet there is no mention of that in checkTrigger. Does it
automagically divine that from the insert statement?

Thanks,
-Alessandro

On Tue, Feb 14, 2012 at 12:40 AM, Bartosz Dmytrak <bdmytrak(at)eranet(dot)pl>wrote:

> 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
>>>>
>>>
>>>
>>
>