Re: Most effective insert or replace

On Fri, 3 Jul 2009, Sergei Politov wrote:
>   As far as I recall postgres does not have built-in support for "insert or replace" feature.

>   Please comment these ways and propose effective ways to simulate "insert or replace" behavior.
>   Also in may case I'm making a lot of inserts in a batch.

A few years ago I researched this, and came up with the following method
as seeming the fastest:

BEGIN;
DELETE FROM table WHERE id IN (big long list);
COPY table FROM STDIN BINARY;
COMMIT;

However, our circumstances may not be the same as yours for the following
reasons:

1. We are updating whole rows indexed by primary key, not just a single
    field in each row.
2. We are able to use the COPY command - indeed we wrote a fair amount of
    Java to enable batching, background writing, and COPY support. See
    http://www.flymine.org/api/index.html?org/intermine/sql/writebatch/Batch.html
    and http://www.intermine.org/
3. HOT has been invented since then, and it won't play well with this
    method.

Matthew

--
 Trying to write a program that can't be written is... well, it can be an
 enormous amount of fun!                 -- Computer Science Lecturer