pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIntentionally inserting duplicates without aborting
2+ messages / 2 participants
[nested] [flat]
* Intentionally inserting duplicates without aborting
@ 1999-08-12 15:59 disser@sdd.hp.com
1999-08-15 13:06 ` Re: [SQL] Intentionally inserting duplicates without aborting Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 1 reply; 2+ messages in thread
From: disser@sdd.hp.com @ 1999-08-12 15:59 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] Intentionally inserting duplicates without aborting
1999-08-12 15:59 Intentionally inserting duplicates without aborting disser@sdd.hp.com
@ 1999-08-15 13:06 ` Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 0 replies; 2+ messages in thread
From: Herouth Maoz @ 1999-08-15 13:06 UTC (permalink / raw)
To: disser@sdd.hp.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1999-08-15 13:06 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-12 15:59 Intentionally inserting duplicates without aborting disser@sdd.hp.com
1999-08-15 13:06 ` Herouth Maoz <herouth@oumail.openu.ac.il>
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