SQLite INSERT OR IGNORE doesn't ignore duplicates

user_2311230 0
Viewed 2183

I'm writing a java program to combine two databases that have the same schema. This is an issue with SQLite or my queries, not the Java code as I was able to replicate this exactly in the DB Browser application.

I have 2 databases: One being merged, one being the destination.

I initially connect to the database being merged then ATTACH the destination database as "dest". I issue the following query to copy the data:

INSERT or IGNORE into dest.AnalogInput SELECT * from AnalogInput where timestamp >= 1600824664131000000 AND timestamp <= 1600824664131000000

This works fine and inserts the data. If I run it again over the same data, it inserts a duplicate into the destination database. I thought the point of "OR IGNORE" was to prevent duplicates? Is my query incorrect or is my understanding of the OR IGNORE incorrect?

EDIT: yes I know the timestamps are the same - it is a general query and I'm just using a couple of samples on a single timestamp to test.

Thanks!

insert sqlite
1 Answers

INSERT OR IGNORE works only when there are unique constraints in the table.
Since there aren't and you can't add any, because SQLite is not that flexible, the only way to do what you want is like this:

INSERT or IGNORE INTO dest.AnalogInput 
SELECT * FROM AnalogInput WHERE ....
EXCEPT
SELECT * FROM dest.AnalogInput
user_104988280
Related