31–37 of 37 posts
Earlier quoted context omitted.
Yeah, 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.
Which other databases support "insert ignore"?
I think PostgreSQL is relatively alone in lacking it.
Earlier quoted context omitted.
Ah. That's a relief, I was a little worried. But that makes it strange that someone, knowing of this limitation, would choose to start with the xml dump format.
I think the fact that this form of dump doesn't contain foreign keys is even more extraordinary. What's the point of having a dump format that you can't use to restore your database properly? Does it have a health warning advising that it's not a proper dump?
Earlier quoted context omitted.
Which other databases support "insert ignore"?
while not that specific syntax, upserts are widely supported. e.g. http://en.wikipedia.org/wiki/Merge_%28SQL%29 I think PostgreSQL is relatively alone in lacking it.
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.
I find the more difficult issue to be rewriting bulk "insert ignore" and "on duplicate key" queries.
Earlier quoted context omitted.
I think the fact that this form of dump doesn't contain foreign keys is even more extraordinary. What's the point of having a dump format that you can't use to restore your database properly? Does it have a health warning advising that it's not a proper dump?
Well, I've had issues with pg_dump's standard format. It can't be directly used to restore a db, one must manually set it to dump a .bak or something else.
I would recommend using pgloader ( http://pgloader.io/ ) instead. It is simple to use in the default case, but can be configured with rules for how data types should be converted if necessary. It can also clean up weird timestamps like 0000-00-00 00:00:00.
Upvote for pgloader. Moving data from SQL Server into Postgres with PGLoader has been fairly seamless and the maintainer is very responsive on Github.