Received: from mx99.openu.ac.il (arbel.openu.ac.il [147.233.32.4]) by hub.org (8.9.3/8.9.3) with ESMTP id JAA76923 for ; Sun, 15 Aug 1999 09:05:12 -0400 (EDT) (envelope-from herouth@oumail.openu.ac.il) Received: from talmon.openu.ac.il (talmon.openu.ac.il [147.233.32.11]) by mx99.openu.ac.il (Postfix) with ESMTP for id 1CDEC4B66B; Sun, 15 Aug 1999 16:04:48 +0300 (IDT) Received: from [147.233.159.109] (mac-herouth.pc.openu.ac.il) by talmon.openu.ac.il (Netscape Messaging Server 3.54) with ESMTP id AAA63E2; Sun, 15 Aug 1999 16:08:24 +0300 X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Sun, 15 Aug 1999 16:06:06 +0300 To: disser@sdd.hp.com, pgsql-sql@postgreSQL.org From: Herouth Maoz Subject: Re: [SQL] Intentionally inserting duplicates without aborting At 18:59 +0300 on 12/08/1999, disser@sdd.hp.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