Re: [GENERAL] newbie question - delete before insert

[Sim Zacks]

How do people implement insert or upate ( otherwise known as upsert ) behaviour 
in postgres i.e. insert a row if it's key does not exist in the database else 
update the existing row?

I tried using an insert rule to delete any existing rows first then insert 
however this leads to infinitely recursive rules ( which postgres properly 
rejects. )

I'm guesisng that the most sensible approach would be a stored proc/function?
I've done it with an on insert trigger. The where clause contains the 
values that make this row unique. If it finds another row with the same 
fields, it doe san update instead of the insert:


CREATE OR REPLACE FUNCTION stock.trg_beforeinsertstock()
  RETURNS "trigger" AS
$BODY$
declare
v_stockid int;
begin
select stockid into v_stockid from stock where pnid=new.pnid
and ownerid=new.ownerid and 
coalesce(stocklocationid,-1)=coalesce(new.stocklocationid,-1)

and coalesce(batchid,-1)=coalesce(new.batchid,-1);
if v_stockid is not null then
Update stock set stock=stock+new.stock where stockid=v_stockid;
return null;
else
return new;
end if;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE;

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] newbie question - delete before insert

[Ashish Karalkar]

On 11/20/2010 02:43 PM, Thomas Kellerer wrote:
> Grant Mckenzie wrote on 20.11.2010 07:00:
>> How do people implement insert or upate ( otherwise known as upsert )
>> behaviour in postgres i.e. insert a row if it's key does not exist in
>> the database else update the existing row?
>>
>
> You can simply send the UPDATE, if nothing was updated, it's safe to
> send the INSERT
>
> Regards
> Thomas
>
>
>
something on the line of 

http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html

*Example 38-2. Exceptions with UPDATE/INSERT*

-- 


With Regards
Ashish Karalkar

Re: [GENERAL] newbie question - delete before insert

[Thomas Kellerer]

Grant Mckenzie wrote on 20.11.2010 07:00:

How do people implement insert or upate ( otherwise known as upsert )
behaviour in postgres i.e. insert a row if it's key does not exist in
the database else update the existing row?



You can simply send the UPDATE, if nothing was updated, it's safe to send the 
INSERT

Regards
Thomas



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] newbie question - delete before insert

[Grant Mckenzie]

Hi,

a question that I imagine is a faq but have not been able to find much help.

How do people implement insert or upate ( otherwise known as upsert ) behaviour 
in postgres i.e. insert a row if it's key does not exist in the database else 
update the existing row?

I tried using an insert rule to delete any existing rows first then insert 
however this leads to infinitely recursive rules ( which postgres properly 
rejects. )

I'm guesisng that the most sensible approach would be a stored proc/function?

Cheers
G.




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general