SQL insert row if field has changed (high performance)
My Table has these fields:
SENSOR_ID INTEGER, SENSOR_READING REAL, TIME_OF_READING TIMESTAMP, HASCHANGED BOOLEAN
Each sensor fires >10'000 readings/day. I want to log only those datapoints where SENSOR_READING is different from the last logged value.
Alternatively, I may log everything but set HASCHANGED to true whenever SENSOR_READING is different from the last logged value.
What is the most performant syntax for accomplishing that (with PostgreSQL, without php logics)?
You could do something like this
insert into readings (sensor_id, sensor_reading, time_of_reading) select 42, 1234.5678, current_timestamp from readings where not exists (select * from readings where sensor_reading = 1234.5678 and time_of_reading = (select max(time_of_reading) from readings where sensor_id = 42);
This will require the sensor_reading column to be indexed but I still doubt that it will actually be faster.
You don't need to store the haschanged attribute because you can calculate that while retrieving the data:
select sensor_id, sensor_reading, time_of_reading, lag(sensor_reading) over (partition by sensor_id order by time_of_reading) = sensor_reading as has_changed from readings;
This assumes that sensor_id isn't actually unique, otherwise you couldn't store more than one reading for a sensor
It further assumes that you change that REAL column to a NUMERIC column, because REAL values using = isn't accurate (actually the storing isn't accurate to begin with)
You should have a table with the sensors and their current readings, and UPDATE it on the sensor event. Then use a trigger to write to the log table if the new reading differs.
I suppose that you have to add a column where place the last value, then at every "read" you can make a comparison between those two columns.
This is a "pure sql" approach but there are solution much better, like callback (you told about php so i suggest symfony and doctrine that can do that for you and this is a valid approach if you have to do some step if the values are changed or something like this...)