4 ms·
I find the more difficult issue to be rewriting bulk "insert ignore" and "on duplicate key" queries.
by codexon 12y ago
I find the more difficult issue to be rewriting bulk "insert ignore" and "on duplicate key" queries.
- tiglionabbit 12y agoYeah, seems like postgres is the only database that doesn't support those things. I mean, I use postgres, but I curse the lack of this feature regularly.
- jaytaylor 12y agoWhich other databases support "insert ignore"?
- mh- 12y agowhile not that specific syntax, upserts are widely supported. e.g. http://en.wikipedia.org/wiki/Merge_%28SQL%29 http://en.wikipedia.org/wiki/Merge_%28SQL%29 I think PostgreSQL is relatively alone in lacking it.
- jaytaylor 12y agogot it, thanks mh-!
- driax 12y agoI have tried to read into why PostgresSQL doesn't yet (they are working it) have upserts. Several times I have come across people discussing that Merge which is much more powerful the simple upserts, also doesn't really mandate the nature of upserts in most databases. Simply a user might expect that upserts always succeed with either an update or insert, and never return an error (except when the consistency model is set high enough). The problem is that many implementations of merge doesn't provide that guarantee, unbeknownst to many users. Or they are simpler databases such as sqlite (which doesn't provide the same multi-user/transaction performance). Postgres, as they are well known for, want to implement it properly. They know that application developers does not expect that they have to resubmit failed upsert. Aside: they are also working on audit features, which presents a number of implementations difficulties that upserts also does. So some time or later, probably not to far away, we will see upserts in postgres. Proper atomic, performant, nice upserts.
- rpedela 12y agoIt is looking like some form of upsert will make it into 9.5, but not confirmed yet.