On 09/10/2010 08:07 PM, tony@exquisiteimages.com wrote:
> I have a situation where I receive a file with transactions that have a
> unique key from a vendor. These transactions should only be imported into
> my system once, but the vendor system will occasionally resend a
> transaction by mistake.
>
> The way I am currently handling this with Micorosft ADO and FoxPro files
> is to open a table with an index on the vendor key and seek on the key. If
> there is no match I add it, if there is a match I put it in an exception
> file to be manually checked.
>
> Using PostgreSQL I can't open a table and do seeks against an index. I
> could do a select against the database and see if 0 records are returned,
> but that seems to take more time than doing a seek on an index. Is there a
> more SQL friendly way of handling this task?
Postgres isn't going to just use the index because it needs the
visibility information in the table. But it will be better to load all
of the data into a staging table using COPY and then insert the missing
rows from there. It will be a ton faster than going a row at a time,
looking for a match then doing an insert.
Scott