Re: Fwd: inserting new data only

On Tue, Feb 25, 2020 at 11:03 AM Przemek Klosowski <
[email protected]> wrote:

> 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?
> _______________________________________________
>


Why not:

CREATE TABLE ME (ATIME TIME, VALUE INTEGER PRIMARY KEY);

You can't INSERT duplicate numbers into the "VALUE" column, it will fail.


-- 
People in sleeping bags are the soft tacos of the bear world.
Maranatha! <><
John McKown
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users