How to avoid duplicate records when using datainsert?

Question

Neil Caithness 2016-1-21

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/264640-how-to-avoid-duplicate-records-when-using-datainsert

评论： Neil Caithness 2016-1-22

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

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Roger Wohlwend 2016-1-22

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/264640-how-to-avoid-duplicate-records-when-using-datainsert#answer_207096

在 MATLAB Online 中打开

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

3 个评论
显示 1更早的评论隐藏 1更早的评论

Neil Caithness 2016-1-22

编辑：Neil Caithness 2016-1-22

在 MATLAB Online 中打开

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 2016-1-22

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.

请先登录，再进行评论。