From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: disser@sdd.hp.com
To: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] Intentionally inserting duplicates without aborting
Date: Sun, 15 Aug 1999 16:06:06 +0300
Message-ID: <l03130306b3dc5e8795a3@[147.233.159.109]> (raw)
In-Reply-To: <u907h9eij.fsf@sdd.hp.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: herouth@oumail.openu.ac.il, disser@sdd.hp.com
Subject: Re: [SQL] Intentionally inserting duplicates without aborting
In-Reply-To: <l03130306b3dc5e8795a3@[147.233.159.109]>
References: <u907h9eij.fsf@sdd.hp.com> <l03130306b3dc5e8795a3@[147.233.159.109]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox