How to avoid duplicate records when using datainsert?

3 次查看(过去 30 天)
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

采纳的回答

Roger Wohlwend
Roger Wohlwend 2016-1-22
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 个评论
Neil Caithness
Neil Caithness 2016-1-22
编辑:Neil Caithness 2016-1-22
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
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.

请先登录,再进行评论。

更多回答(0 个)

类别

Help CenterFile Exchange 中查找有关 Database Toolbox 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by