[Feature Request] Add option to ignore duplicate keys for bulk inserting documents

[tobiasdam]

ArcadeDBs SQL INSERT INTO ... CONTENT has the ability to insert multiple records by specifying an array of objects.

Unfortunately, the execution is stopped on any error as stated in the docs:

If multiple documents are inserted as JSON "CONTENT", and one document causes an error, for example due to a schema error, the remaining documents in the JSON array are not inserted.

Adding an option to ignore duplicate key errors (com.arcadedb.exception.DuplicatedKeyException) would come in handy as it reduces the requests that need to be sent to the server.

ArangoDB for instance has a similar option.

Alternative options I can think of:

• Enable bulk inserts via sqlscript. This might be done by adding some TRY ... CATCH logic.
• Add a batch importer for documents as is exists for edges and vertices: https://docs.arcadedb.com/arcadedb/reference/http-api/http#http-batch

[lvca]

Thanks for the detailed request. Here is the direction I'd like to take.

We'll add an explicit clause to INSERT that skips records whose unique key already exists, instead of aborting the whole batch:

INSERT INTO Product CONTENT [ {...}, {...}, {...} ] ON DUPLICATE KEY SKIP

Semantics:

• Records that would violate a unique index (duplicate key) are silently skipped.
• All the other records in the array are inserted.
• The command reports how many records were inserted and how many were skipped, so you always know what happened.

A couple of design notes:

• The scope of v1 is strictly duplicate key conflicts (unique indexes). Other errors (mandatory field missing, type mismatch, etc.) will still stop the command, because silently swallowing those would hide real data problems.
• Using SKIP (rather than IGNORE) leaves the door open to a future ON DUPLICATE KEY UPDATE variant for a real upsert, sharing the same syntax.

This keeps a single request/single transaction for the whole array (great for bulk loading) while giving you the "insert what you can, skip the dupes" behavior, similar to ArangoDB's overwriteMode: ignore and Postgres' ON CONFLICT DO NOTHING.

I'll keep this issue for tracking and target it for a coming release.