pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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





view thread (2+ messages)

Message-ID: <l03130306b3dc5e8795a3@[147.233.159.109]>
Permalink:  ../l03130306b3dc5e8795a3@%5B147.233.159.109%5D/
Also on:    postgresql.org/message-id/l03130306b3dc5e8795a3@[147.233.159.109]

 · 

reply

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