Обсуждение: newbie question - delete before insert
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 keydoes 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.
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
On 11/20/2010 02:43 PM, Thomas Kellerer wrote:
http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html
Example 38-2. Exceptions with UPDATE/INSERT
Grant Mckenzie wrote on 20.11.2010 07:00:something on the line of ....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
http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html
Example 38-2. Exceptions with UPDATE/INSERT
-- With Regards Ashish Karalkar
> How do people implement insert or upate ( otherwise known as upsert ) behaviour in postgres i.e. insert a row if it's keydoes 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;