On 09/10/2010 08:07 PM, tony@xxxxxxxxxxxxxxxxxxx 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
