Any simple way of only inserting a record if it doesn't already exist?

[mowings]

I tried to use a unique index that describes the keys that make a record distinct, but unless I abort the transaction (which I don't want to do because this is a bulk insert, and I'd lose 100s of good inserts), the row still gets inserted -- even though ql reports the unique index error. I'm not sure if this is a bug or not -- I would think that if the insert returned an error, then the row insert would fail -- but it does not.

I tried querying prior to the insert, but this massively slows things down. Any suggestions?

Thanks very much

[cznic]

Do you have an example of how this is usually done in other RDBMs? Then I would like to figure out if this is a feature request or a bug report (or both).

the row still gets inserted -- even though ql reports the unique index error

If there's a bug in inserting into a table with unique index please fill in a separate issue for that. Of course, a small, self-contained reproduction case is needed to investigate further.

Thanks!

[mowings]

Most of my experience is with postgres -- ordinarily an error in a transaction (including violating a unique index on an insert) would rollback the entire transaction. I think this is probably standard behavior for most databases. I'll try to get an example together and post it here. Note, of course, that this would not have gotten me the the behavior I wanted in my bulk inserts anyway. I'm pretty sure not aborting the transaction in this case is a bug, though.

What is probably wanted (as a feature) is an UPSERT kind of operation. The latest version of postgres supports an ON CONFLICT operator that can allows you to specify an action (update, nothing, etc) to execute when an insert fails. You could use a conditional insert/update in prior versions (the insert would only occur if the update updated 0 rows).

MySQL does upserts via an ON DUPLICATE KEY UPDATE statement.

[cznic]

Thanks for the info. I think the proper order is to first fix the existing bug and then decide how to design the feature.

Then comes the hard part - implementing it ;-)

BTW, do I understand correctly that what you are after is INSERT ON CONFLICT IGNORE, correct? If so, then the minimal subset to implement could possibly be the sqlite's flavor of the same, spelled INSERT OR IGNORE. What you you think?

[mowings]

I think in my case, a CONFLICT IGNORE would be fine. But I'm pretty sure folks will want the remainder of the CONFLICT semantics in sqlite (CONFLICT REPLACE, CONFLICT ROLLBACK, etc). But IGNORE would be an awesome start (and probably be easier to implement), and would probably cover 75% of the ON CONFLICT use cases.

[cznic]

Good news ;-)

[cznic]

@mowings

Gentle ping. Looking forward the repro case / description of the bug ;-)