create table data
(
key text primary key,
data integer not null
)
without rowid;
-- insert into data select (?, ? as value where value IS NOT (select data from
(select max(key), data from data));
insert into data select '10:32', 12 as value where value IS NOT (select data
from (select max(key), data from data));
insert into data select '10:35', 15 as value where value IS NOT (select data
from (select max(key), data from data));
insert into data select '10:37', 15 as value where value IS NOT (select data
from (select max(key), data from data));
insert into data select '10:39', 13 as value where value IS NOT (select data
from (select max(key), data from data));
insert into data select '10:43', 13 as value where value IS NOT (select data
from (select max(key), data from data));
insert into data select '10:46', 18 as value where value IS NOT (select data
from (select max(key), data from data));
select * from data;
10:32|12
10:35|15
10:39|13
10:46|18
Constraints:
(1) Will only work for appending data (new key > all keys in table)
(2) Types of key and data are immaterial as long as you are only inserting
(appending) new keys.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Przemek Klosowski
>Sent: Tuesday, 25 February, 2020 10:02
>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>Subject: [sqlite] Fwd: inserting new data only
>
>I am storing time series data arriving from a sensor into (time,value)
>records, like so:
>10:32 12
>10:35 15
>10:37 15
>10:39 13
>10:43 13
>10:46 18
>
>and I want to avoid storing repetitive data, so that the database should
>contain
>10:32 12
>10:35 15
>10:39 13
>10:46 18
>where only the earliest time with the unchanging value is stored.
>
>I don't see how INSERT could be conditional on e.g. value != (select
>value from tbl order by time descending limit 1), so I thought I'd use
>triggers. The only way I could think of was to delete the new
>duplicate record after it has been inserted:
>
>create trigger cull after insert on tbl when
> (select value-lead(value) over (order by time desc) from a limit 1) = 0
>begin
> delete from a where time like new.time;
>end;
>
>Is there a simpler way?
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
