How to avoid duplicate records when using datainsert?

datainsert fails when it encounters any duplicate records. Is there a way to specify it should just skip the duplicates, perhaps returning an index of the successful inserts?

datainsert(conn,'consdata',colnames,tbl)

Error using database.ODBCConnection/insert (line 264)
ODBC Driver Error: ERROR: duplicate key value violates unique constraint "consdata_pkey";
Error while executing the query

Answer 1

[Roger Wohlwend]

No, you can't specify something like that. If you want datainsert to continue with the next dataset if it encounters a duplicate, just use try-catch.

try
   datainsert(conn,'consdata',colnames,tbl)
catch E
   % This section is only executed if a duplicate record exists. So if you want
   % Matlab to remember when datainsert failed because of a duplicate record,
   % put some code here. For expample if you execute this try-catch within a 
   % Loop, write the Loop index to a vector.
end

[Neil Caithness]

Thanks Roger,

So the options for bulk inserting 250,000 records don't look attractive.

1. Pull all the existing records from the db and check for duplicates using setdiff (I don't fancy the performance).

2. Loop over the set and do one at a time catching the errors as you suggest.

Or is there something more attractive?

Regards Neil

[Neil Caithness]

I guess I could use datainsert into an empty table with no primary keys to obstruct the process, and then do an sql insert into the target table.

INSERT INTO table1
(SELECT * FROM table2
 WHERE name NOT IN
     (SELECT name FROM table1));

Maybe the performance of this won't be too bad.

Now I really need an answer to my second question Does the Database Toolbox support sql numeric array data types ?

[Neil Caithness]

For completeness I thought I'd add this just found in the PostgreSQL 9.5 documentation:

ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome; provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency. This is also known as UPSERT — "UPDATE or INSERT".

I'm guessing this will be the best of the available options.