Received: from hpsdlo.sdd.hp.com (hpsdlo.sdd.hp.com [15.255.160.64]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA34050 for ; Thu, 12 Aug 1999 11:59:03 -0400 (EDT) (envelope-from disser@sdd.hp.com) From: disser@sdd.hp.com Received: from hpsdly.sdd.hp.com (root@hpsdly.sdd.hp.com [15.26.144.61]) by hpsdlo.sdd.hp.com (8.8.6 (PHNE_14041)/8.8.5btis+epg) with ESMTP id IAA22228 for ; Thu, 12 Aug 1999 08:59:02 -0700 (PDT) Received: from HPSDS069 (hpsds069.sdd.hp.com [15.60.145.194]) by hpsdly.sdd.hp.com with SMTP (8.7.6/8.7.3 TIS 5.0/sdd epg) id IAA11454 for ; Thu, 12 Aug 1999 08:59:01 -0700 (PDT) Sender: ddisser@HPSDS069.sdd.hp.com To: pgsql-sql@postgresql.org Subject: Intentionally inserting duplicates without aborting Date: 12 Aug 1999 08:59:00 -0700 Message-ID: Lines: 41 X-Mailer: Gnus v5.3/Emacs 19.34 I'm fairly new to SQL, so please bear with me. I'm writing an application in which I represent an item in a table, and I would also like a history table in which I represent the item as it exists on various dates. The item's primary key is an integer. I have represented the history table with a compound key of the item's primary key and a column indicating the state of that item. Eg. create table item ( item_id integer primary key, state varchar(8) ); create table item_hist ( item_id integer primary key, state varchar(8), date date, primary key (item_id, state) ); It happens that I have to query another application for the status of these items, and it is vastly more efficient to get the status of all the items than it is to query for each individually. 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. --Dave