4 ms·
I could really use UPSERT. I'm using postgresql right now. My use case: I could have a table like: create table items (id uuid primary key, attributes js
by joevandyk 13y ago
I could really use UPSERT.
I'm using postgresql right now.
My use case: I could have a table like:
create table items (id uuid primary key, attributes json);
I'd have a webapp using javascript for adding/editing new items. If the user wants to add a new item, the client code would generate the uuid.
Editing and adding new rows would both use the same UPSERT/MERGE code -- no need for separate insert or update statements -- if the uuid already exists, it's assumed to be an update, if it doesn't, it's inserted as a new row.
- baudehlo 13y agoFor 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.
- ocharles 13y agoThis is very susceptible to race conditions, which is why many of us want true UPSERT semantics. http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE http://www.postgresql.org/docs/current/static/plpgsql-contro... is the current best way to do UPSERT in PG.
- epo 13y agoSo if there is a way to do UPSERT what is the problem? Are people just complaining about the lack of syntactic sugar?
- masklinn 13y ago> 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.
- fusiongyro 13y agoLast time I looked into this, a big thing stopping it from happening was that the SQL standard for this kind of sucks and there is no consensus for a better syntax among the databases that implement it. I would also love to see it, and I'd be amazed if it were as technically complex as, say, materialized views.
- ef4 13y agoUpsert is already possible in a single statement, though a bit unwieldy. create table people (uuid varchar, name varchar); with new_rows as (select (unnest(ARRAY[('xxx','John Smith'), ('yyy', 'Ben Bitdiddle')]::people[])).*), updated as (UPDATE people SET name=new_rows.name FROM new_rows WHERE people.uuid=new_rows.uuid RETURNING people.uuid) insert into people select new_rows.* from new_rows left join updated using (uuid) where updated.uuid is null;
- psadauskas 13y agoHere's an UPSERT in postgresql: https://gist.github.com/paul/855efdecaaa2ec4deec7 https://gist.github.com/paul/855efdecaaa2ec4deec7 While we're at it, here's a "find or create" in a single atomic statement: https://gist.github.com/paul/75ec84d131e36492b17b https://gist.github.com/paul/75ec84d131e36492b17b
- ocharles 13y agoI believe both of these still have race conditions, and are not the same as truly atomic upserts. Granted the window is considerably smaller, but their is still a race condition.