Re: mysql replace in postgreSQL?

On Sun, Oct 30, 2005 at 03:52:23PM +0100, Tino Wildenhain wrote:
> Am Sonntag, den 30.10.2005, 06:29 -0800 schrieb David Fetter:
> > On Fri, Oct 28, 2005 at 09:57:03PM -0400, blackwater dev wrote:
> > > In MySQL, I can use the replace statement which either updates
> > > the data there or inserts it. Is there a comporable syntax to
> > > use in postgreSQL?
> >
> > Not really, but here's an example which doesn't have the
> > brokenness of MySQL's REPLACE INTO and doesn't have the race
> > conditions that some others' proposals have.
> >
> > http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
> >
> > Of course, it's not as nice and flexible as the SQL standard
> > MERGE, but until that day comes, you can use that example.
>
> In most cases, just DELETE and then INSERT should work perfectly.
> (UPDATE and MERGE would cause dead tuples in the same way so in the
> end they are only syntactical sugar)
>
> Another way is a rule for insert which turns it into an update in
> case the desired tuple is already existent.

That has a race condition in it. What happens if something deletes
the tuple between the attempted INSERT and the UPDATE?

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!