News for hackers. Stuff that matters.
Re:PostgreSQL 9.3 Beta 1 Released by baudehlo on Monday May 13, 2013 @03:39PM (#5700928)
For this I currently use:

    UPDATE Table SET ... WHERE id=$1
    if (rows_updated == 0) {
        INSERT INTO Table ... WHERE NOT EXISTS (SELECT 1 FROM Table WHERE id=$1)
        if (rows_inserted == 0) {
            throw "Conflict!"
        }
    }
It's a bit of a hack, but works well enough. I agree would be nice to have a more transparent way of doing it.
Re:PostgreSQL 9.3 Beta 1 Released by ocharles on Tuesday May 14, 2013 @04:57AM (#5703683)
This is very susceptible to race conditions, which is why many of us want true UPSERT semantics. http://www.postgresql.org/docs/current/static/plpgsql-contro... is the current best way to do UPSERT in PG.
Re:PostgreSQL 9.3 Beta 1 Released by epo on Tuesday May 14, 2013 @08:57AM (#5704516)
So if there is a way to do UPSERT what is the problem? Are people just complaining about the lack of syntactic sugar?
Re:PostgreSQL 9.3 Beta 1 Released by masklinn on Tuesday May 14, 2013 @11:53AM (#5705717)
> what is the problem?

Quoting ocharles:

> susceptible to race conditions, which is why many of us want true UPSERT semantics

The snippet provided in the postgres doc is the current best, but it's still has a number of flaws:

> This coding assumes the unique_violation error is caused by the INSERT, and not by, say, an INSERT in a trigger function on the table. It might also misbehave if there is more than one unique index on the table, since it will retry the operation regardless of which index caused the error. More safety could be had by using the features discussed next to check that the trapped error was the one expected.