Re: [SQL] Intentionally inserting duplicates without aborting

At 18:59 +0300 on 12/08/1999, disser(at)sdd(dot)hp(dot)com wrote:

> What I would like to do (and have manage to kludge together in Perl
> DBI) is to attempt to insert (item_id, state, today's date) into
> item_hist, and in the cases where that item/state combo exists
> already, the insert will fail. However, if I am using {AutoCommit =>
> 0}, the whole transaction bombs, so I can only get away with this if
> I'm AutoCommit'ing.
>
> Any thoughts on how I can do this with AutoCommit => 0? I would
> rather not have to look up all the status rows to figure out if an
> error will occur.

Are you saying that inserting a (some_item_id, some_state, some_date) and
waiting for an error is more efficient than doing a SELECT 1 FROM ... WHERE
item_id = some_item_id, state = some_state and seeing whether or not you
got any rows? It shouldn't be much of a difference, since both operations
simply go through the index.

In any case, if what you want to do is to insert a bulk of data, and only
have the non-duplicates be inserted, then perhaps you should create a temp
table of ited id and state, insert all the data into it (using COPY for
faster insertions) and then:

INSERT INTO item_history
SELECT item_id, state, now() FROM temp_table t
WHERE NOT EXISTS (
SELECT * FROM item_history i
WHERE i.item_id = t.item_id AND i.state = t.state
);

Or maybe you meant that the insertion is done directly from the table item?
Then you don't need a temporary table. The general looks are the same,
though you would probably want to add things to the WHERE clause.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma