50 ms·
Postgres gets support for upsert
- ozgune 12y agoSo excited about upserts in Postgres! This was the most requested feature in PostgreSQL: https://postgresql.uservoice.com/forums/21853-general https://postgresql.uservoice.com/forums/21853-general
- anilshanbhag 12y agoThis is actually huge. A common problem that arises when you write applications is you want to INSERT if key does not exist else UPDATE. The right way of doing this without an upsert is using a transaction. However this will make life easier as you can do it directly in one SQL statement.
- ryan0x00 12y agoi've seen a system in mysql do "insert on duplicate key update" for ages, so this is that same pattern?
- girvo 12y agoYeah, it is the same as INSERT ... ON DUPLICATE KEY UPDATE, and similar to REPLACE in MySQL. Exciting to see it in Postgres!
- kkland 12y agoI think the way MySQL works in doing "INSERT ... ON DUPLICATE KEY UPDATE" is to delete the old row and insert a new row if there's an auto incremental column (mostly it's ID column). Being curious if Postgres works the same way in this regard.
- dugmartin 12y agoNo, the auto increment columns are maintained as is.
- kkland 12y agoThat's great. Otherwise, it just wastes IDs unless one figures out a workaround for it.
- girvo 11y agoNo, that's how REPLACE works -- which is a complete pain to be honest! INSERT ... UPDATE works the way you'd hope it would, and keeps the auto inc columns correct.
- colanderman 12y agoJust "using a transaction" is insufficient. You must be prepared to handle the case that neither the INSERT nor the UPDATE succeeds (with READ COMMITTED isolation), or that the transaction fails (with REPEATABLE READ isolation or better), by repeating the transaction. And if latency is at all a concern to you, you must wrap this all in a stored procedure to avoid the necessary round-trip-time between the commands. Hence this is more than just saving typing a couple lines -- this saves writing entire stupid loops to do what is conceptually a simple (and very common) operation. Postgres gets better and better.
- lobster_johnson 12y agoIt's fairly trivial to do with a savepoint, but it's definitely not performant, and requires some pretty stupid client-side error-handling. It will also cause your Postgres logs to be littered with benign but misleading error messages (possibly these could be silenced, I never bothered to find out). So, yes, this is great news.
- spacemanmatt 12y agoMinor nit: it's performant and doesn't pollute logs to implement this on the server side. I've written macros to make gen'ing upsert functions easy, given a certain table pattern. But this definitely cleans that situation up as well as makes it accessible to more casual developers.
- lobster_johnson 12y agoYes, sorry, I was referring to doing it client-side.
- saurik 12y ago...and then you additionally have to verify that the specific reason both the update and the insert failed was due to concurrency, or you can make the mistake I did a couple years ago where I had transactions spinning against my database for weeks on end due to an unrelated constraint failure I had failed to notice with some transactions that I kept retrying in a loop as part of my hacked together upsert implementation (which is why my user id numbers jumped from ~8m to ~460m... it is a meaningless mistake, but one I am reminded of every day due to that "46" staring at me :/).
- NoMoreNicksLeft 12y agoWhy do you need to use a transaction for that? Do the update statement, followed by insert-where-not-exists. If the update doesn't match, 0 rows updated, and the insert works. If the update matches, then 0 rows inserted.
- anarazel 12y agoThat's unfortunately not correct. The WHERE NOT EXISTS(...) will not "see" rows inserted by concurrently running transactions that have not committed yet.
- andrewstuart2 12y agoSeriously, IIRC the wiki page for this feature was like 5 years old. I'll have some statements I'll be very glad to update as soon as this is released. I was so surprised when I ran into the lack of this feature as I'd had it on both MSSQL and MySQL.
- DigitalSea 12y agoYet another reason why I absolutely love Postgres. It might have taken four years, but we finally got what many of us have been asking for. Nothing ruined my day more than having to write insert-update loops, I am beyond ecstatic for this.
- dan15 12y agoYou love Postgres because it takes four years to get requested features?
- jsmthrowaway 12y agoPostgres turned a corner around the 9.0 era and has been churning out huge features for years. Indexes on jsonb completely changed the game for me. As you can see from the commit, too, this was not a trivial feature. So yes, I'm overall pleased.
- pilif 12y agoIf given the choice between do it half-assed but quickly and do it right but using how ever much time is required to arrive there, the PostgreSQL team always choses the latter approach. This conservatism works well for me considering we're talking about a database here. One of the contributors has written an interesting article explaining why upsert is difficult to get right (if by "right" you want it to complete reasonably quickly and without any chance of corrupting your data): http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ http://www.depesz.com/2012/06/10/why-is-upsert-so-complicate...
- colanderman 12y agoBecause the devs pay attention, and take the time to do things right. See https://wiki.postgresql.org/wiki/UPSERT https://wiki.postgresql.org/wiki/UPSERT for a glimpse into the design that needed to go behind this. I feel a lot of Postgres fans (myself included) put their "money" on Postgres circa late version 7 or early version 8, back when MySQL was the more featureful and performant of the two, while Postgres had the reputation for being more, shall we say, robust. (Remember, those were the days before InnoDB was the default in MySQL.) The payout for investment in Postgres the past few years has been substantial -- native replication, true serializable transactions, foreign data wrappers, index-only scans, native JSON support, updatable views, and materialized views are among the features added to Postgres in the last 4 years. It's matured from being an "entry-level" RDBMS with few features that all work reliably, to a much more enterprise-friendly RDBMS with many features that still work reliably.
- karmakaze 12y agoYes! it's about bloody time.
- pbnjay 12y agoI think I squealed with delight when I read this title. This is really a great addition, and will simplify so much of my code once I'm able to upgrade. I try not to think too deeply about all the DB internals (some people are much smarter than me about this stuff), but clearly there are a lot of things that need maintained internally to make this correct, so kudos to all the people who contributed to this.
- craigkerstiens 12y agoA big +1 to this. Peter Geoghegan is the primary author and has been championing it for over 2 Postgres releases now. I know he's especially excited to see it land. Also it couldn't have been possible without the support of Heikki and Andres (who actually submitted this to HN as his first ever submission).
- anarazel 12y agoHah, where from do you know that I don't have my sock puppet accounts ;)
- olalonde 12y agoWhich version of Postgres will this land in?
- whistlerbrk 12y agoFor anyone who has ever had to write scripts to import incremental batch data, CSV, etc this seems huge.
- nsxwolf 12y agoI come from a SQL Server background and recently jumped over to a Postgres shop. It seems like I came in at a really exciting time. I was really missing SQL Server's powerful MERGE statement, so this is welcome news.
- silon3 12y agoDoes it handle locking (aka no deadlocks) correctly?
- amitlan 12y agoThere is a section in UPSERT wiki about this - https://wiki.postgresql.org/wiki/Value_locking#.22Unprincipled_Deadlocking.22_and_value_locking https://wiki.postgresql.org/wiki/Value_locking#.22Unprincipl... EDIT: rather the whole "Value Locking" page - https://wiki.postgresql.org/wiki/Value_locking https://wiki.postgresql.org/wiki/Value_locking
- viraptor 12y agoI love that it's there now. I've been waiting for it for a long time, but one thing I don't get is... why does every single implementation have to have their own slightly different syntax? pgsql -> on conflict mysql -> replace / on duplicate oracle -> merge mssql -> merge sqlite -> insert or replace firebird -> merge / update or insert
- anarazel 12y agoMERGE is actually a separate feature. We're still considering implementing it. But it's syntax is too cumbersome for many usages. There's also some unrelated complexity - so implementing merge support does get easier by the infrastructure in here, but a fair amount of work remains.
- ryanjshaw 12y agoThis seems MUCH better than MERGE for the common case. I tried implementing some MERGE logic on MSSQL recently and at first it seemed great until I realised: (a) maintaining/debugging a MERGE that uses most of its syntactical features is an absolute nightmare involving psychically debugging 20+ lines of opaque code - I ended up copying & pasting pieces of the statement into temporary tables/variables and confirming results that way, making me question the value of MERGE as the performance was similar to the original code, but less flexible (MERGE doesn't make available some of the values you need in some cases, so you need to do pre-mapping of data in those cases anyway, resulting in lots of similar code) (b) it doesn't save you from needing to know and use the correct locks, something many people don't seem to realise! I then discovered this page, listing all kinds of issues with the MERGE implementation (even on MS SQL 2014, years after MERGE had been introduced): http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ http://www.mssqltips.com/sqlservertip/3074/use-caution-with-... I realised that even if I followed my debugging approach in (a), one day I was going to run into a problem with MERGE that couldn't be replicated in decomposed statements because MERGE was doing 'something' else. And course, if you look at some of those issues, many of them suffer from the typical Microsoft "Won't fix and won't say why" attitude. In the end I decided to just keep the original/decomposed code, which was clearer and easier to work with. MERGE is probably okay if you're staging data; I wouldn't use it for transactional processing. It is far too complicated. I really like that PG has focused on efficiently implementing the common use case and avoided the kitchen sink that is MERGE.
- taspeotis 12y agoWho decided the syntax? Why not use MERGE ... INTO ... USING ... ON ... WHEN like Oracle and MSSQL and apparently ANSI SQL [1]? I'm not aware of ON CONFLICT being standard. [1] http://en.wikipedia.org/wiki/Merge_(SQL) http://en.wikipedia.org/wiki/Merge_(SQL)
- veddan 12y agoThere's some extended discussion of the issue on the wiki[1]. A future full MERGE implementation might have slightly different semantics or performance characteristics than ON CONFLICT (which might also differ from other DBMS). [1] https://wiki.postgresql.org/wiki/UPSERT#SQL_MERGE_syntax https://wiki.postgresql.org/wiki/UPSERT#SQL_MERGE_syntax
- smegel 12y agoI remember about 10 years ago using UPSERT in a Teradata database to do delta-diff updating of tables. Basically, I was updating a table from an external file, and I wanted to add what was new, leave what was the same, and delete any records no longer in the external table. Using UPSERT to insert new rows, update a timestamp on existing rows, and then afterwards delete any rows who's timestamp was before the UPSERT operation. Because of the nature of the file, doing an external patch/diff approach was difficult, but the UPSERT operation worked like a breeze!
- petergeoghegan 12y agoThe documentation covering the new feature has now built: http://www.postgresql.org/docs/devel/static/sql-insert.html http://www.postgresql.org/docs/devel/static/sql-insert.html
- electrotype 12y agoPostgres's documentation is one of the best I've seen. Thanks to everybody working on it!
- abannin 12y agoI want to give a giant digital hug to all PG committers who made this happen.
- deleted 12y ago[deleted]
- donw 12y agoDamnit, I just wrote a sweet gem that handles this... ah, okay, but it requires an index constraint, whereas I needed to merge on arbitrary conditions. So I didn't totally waste my time. Way to go Postgres team!
- loopdoend 12y agoIs the gem open source? I checked your github and didn't see it, I think people would still find it useful!
- kbendyk 12y agoI know it doesn't matter to some developers out there, but for me, the only thing left is the ability to add a column after another one. Even if it's only logical order, it's easier for me to read a table structure when fields are ordered nicely.
- narsil 12y agoUnsure what you mean. A subset of columns can be selected in any order, across tables. Were you referring to the select order used by "*"?
- munro 12y agohttps://wiki.postgresql.org/wiki/Alter_column_position https://wiki.postgresql.org/wiki/Alter_column_position
- pmontra 12y agoYes, I think it's about changing the default presentation order. That could be a feature of the client, psql, pgcli, pgadmin3, but it won't be portable across them unless it's some metadata stored inside the db. Is there any db with that feature? Edit: munro answered that. PostgreSQL does it but it's expensive because it has to rewrite the table.
- kbendyk 12y agoYes, MySQL: ALTER TABLE t1 ADD COLUMN `count` SMALLINT(6) NOT NULL AFTER `lastname`
- kbendyk 12y agoI know, but when I look at a table structure in PgAdmin, all newly added columns appear at the end, of course this can handled properly in select col2, col1... , or by creating a view, but by any other way as this wiki [1] page says. But during development, ability to create logical column order is somehow important to me. [1]https://wiki.postgresql.org/wiki/Alter_column_position https://wiki.postgresql.org/wiki/Alter_column_position
- 12y ago
- ianamartin 12y agoIn my crazy-ass opinion, if you need upserts, there are problems with your application logic and problems with your database. Get off my lawn, etc.
- Nullabillity 12y agoSyncing data from a third-party API?
- spacemanmatt 12y agoIn a concurrent world, it's a reality you eventually have to deal with.
- taco_emoji 12y agoWhy bother posting if you're not going to explain yourself? It's 2015, nobody's going to be impressed by mere contrarianism.
- DotHide 12y agoGreat! I'm just like it!
- deleted 12y ago[deleted]
- ivoras 12y agoDoes the description seems like it's a two-pass operation to anyone else? Can anyone with more detailed knowledge tell why can't this be a single-pass "INSERT in a unique index, UPDATE if it fails" operation?
- anarazel 12y agoTo understand the problem one first has to know that for better or worse postgres does not use index organized tables. Additionally this ought to work not only on a primary key but also on additional unique constraints. Including partial unique indexes, potentially over expressions (say lower(username) or something). That makes some approaches harder. The bigger problem is that 'update if fails' is easier than it sounds. When you find a conflicting row in the index, that row's creating transaction might not yet have committed. So you need to first wait for it to commit, and then retry. But that implies that the transaction could actually roll back or delete the row again. And suddenly there's not actually a row to UPDATE. Thus you need to retry, right? There's also some issues around avoiding deadlocks. If you "naively" just acquire a 'value lock' on the to-be-inserted row, you can easily get into deadlock territory if more than one row is inserted in one transaction. Such value locks have to be released after an attempt to update. Which then necessitates a looping attempt... Does that start to explain the problems a bit?
- morekozhambu 12y agoSlightly OT, why do we see these errors with Oracle's MERGE statements? ORA-01779: cannot modify a column which maps to a non key-preserved table ORA-30926: unable to get a stable set of rows in the source tables
- njharman 12y agoAsking a support question on a feature announcement post for a completely unrelated product is EXTREMELY off topic.
- morekozhambu 12y agoIt was not meant as a support question. It was in the context of MERGE/UPSERT was I asking. I wanted to know if similar thing happens in PG.
- agotterer 12y agoDoes anyone know when this is expected to be released?
- elchief 12y agoNew versions of Postgres usually ship in September. Except 9.4, which slipped to December.
- endymi0n 12y agoFor the backstory on why this has taken so long: http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ http://www.depesz.com/2012/06/10/why-is-upsert-so-complicate...
- deleted 12y ago[deleted]
- Mister_Snuggles 12y agoThis reminds me of a common idiom in the Pick-style databases that I've worked on... READU REC FROM FILE,KEY ELSE REC<1> = 'stuff' * Set other fields as appropriate for a brand new record - this is the 'insert' END REC<1> = 'blah' * Set other fields as appropriate for an update WRITE BLAH TO FILE,KEY ;* Write record and release locks This can, of course, be wrapped in a transaction.
- morekozhambu 12y agoSo to simplify, what I understand is 1. INSERT .. ON CONFLICT is an implementation UPSERT technique 2. MERGE is something meant to be different from UPSERT but has UPSERT feature?
- jeltz 11y agoBasically true except for many implementations of MERGE not being concurrency safe, which means you would have to loop anyway on unique violation making it no better than the CTE based techniques that already exist in 9.4.
- asnyder 12y agoAs someone that's been using PostgreSQL way before it was cool (since 2005), I'm super happy to see this. Previously I had to do EXISTS, IF FOUND UPDATE ELSE INSERT. Now I can just do upsert, so nice. Very thankful to all the hard working Postgres devs. PostgreSQL was really good when I first started using it, and just keeps getting better. Glad to see the general community finally getting on the PostgreSQL bandwagon. Now let's see if I can get more people to use internal PostgreSQL functions instead of constantly writing statements directly in their code.
- corford 12y agoI've been looking forward to this for ages! The solution we use at the moment for upsert is a create or replace rule on the table but it isn't great as you need to remember to update the rule whenever you change the table schema... CREATE OR REPLACE RULE "replace_row" AS ON INSERT TO my_table WHERE EXISTS(SELECT 1 FROM my_table WHERE id=NEW.id) DO INSTEAD (UPDATE my_table SET col1=NEW.col1, col2=NEW.col2, col3=NEW.col3, WHERE id=NEW.id);
- anarazel 12y agoThat unfortunately is not safe on several fronts. For one, the WHERE EXISTS() will not see concurrent insertions by transactions that are still in progress, which means you'll still get constraint violation errors. For another, rules generally have very surprising behaviour. E.g. with this RULE you'll get into trouble if "id" isn't passed in as a explicit value, but uses a non-deterministic DEFAULT or directly passed in expression. Every reference to NEW.id will not be replaced by the result of that expression, but rather with the expression itself. For the common serial id column (aka autoincrement) you'll have a separate nextval() call in each reference. Which can make this explode in pretty damn confusing ways.
- corford 12y agoUnfortunately, there's no good all round solution for upsert in current postgres versions. For our use case (one logger daemon feeding results off a message queue in to a table in an idempotent way) this approach was the simplest and worked the best.
- anarazel 12y agoCan't you just do the "usual" looping pattern around a subtransaction? If events are inserted by a single callsite that shouldn't prove to onerous? C.f. http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html http://www.postgresql.org/docs/9.4/static/plpgsql-control-st...
- 12y ago
- piker 12y agoI am extremely happy to hear about this. We currently insert, catch the exception and update if necessary. That can lead us to race conditions in concurrent transactions. Thank you very much for your excellent work on Postgres. It continues to be an amazing product to work with.
- lcfcjs1 12y agolol we got that a year ago by using mongodb
- jedi_master 12y agoits about F*G time
- rabee 12y agoOh happy day. PostgreSQL is the best.
- ExpiredLink 11y agoSay goodbye to CRUD. Because 'create' and 'update' are syntactically and semantically the same, right?
- DiabloD3 11y agoWhen is Postgres going to get built in fault tolerant multi-master support in any form?
- ape4 11y agoI think an UPSERT keyword would be more clear.
- pcl 11y agoUpserts are generally useful tools for applications that run in active-active multi-datacenter configurations. Have you guys explored the implications of this feature for such deployments on top of Postgres yet?
- qubyte 11y agoI found engineering around the lack of this feature very upserting. I'll get my coat.