Re: on duplicate key

On Thu, Sep 25, 2008 at 10:25 AM, A B <gentosaker(at)gmail(dot)com> wrote:
> Hello.
> I was just asked by a mysql-user how do you do
> insert ..... on duplicate key update ....
> (or however they have it in mysql) in postgresql, if you are going to
> Is it correct to assume that a function that is searching for the key
> and then choosing to insert or update depending on what it found, is
> about as fast as doing an insert within a begin- exception-end
> statement or are there some inherent speed differences?

none really. They're both usually quite fast.

> What should I answer the mysql-user?

You should ask him what happens if you setup a benchmark on mysql and
postgresq that runs 500 of these queries in parallel thousands of
times, and halfway through pull the plug.

Oh wait, I forgot, he's running mysql, so he probably doesn't actually
care about data integrity. :) P.s. I'm mostly just kidding around,
but in all seriousness, he's putting the cart ahead of the horse here.

> By the way, is there any work done on getting this functionality? I
> must admit that it would be handy some times ;-)

I don't know, I find running an update followed by an insert if the
update returns 0 rows is pretty easy to do. From external code or
your favorite pl/lang.