Postgres - detect change in JSONB column value from 'null' to some value across rows I have to work with a table that looks like this:



The actual table is obviously much larger, but for sake of discussion, is there any way to elegantly query the difference between when { a: null } turned into { a: "something"? }

My instinct is to try something (probably pseudocode) like this:

select (select update_time as finish_time from fun_table where json_data -&gt; 'a' != 'null' as tableA) - (select update_time as start_time from fun_table where json_data -&gt; 'a' = 'null as tableB) from tableA, tableB


But there are a lot of different JSON objects in the actual json_data..."b", "c", etc. So again, is there a better / cleaner way to find the difference between the timestamps when "a" turned from "null" to "something"?
JSONB supports equality checks. So, if you're using it, you need only 


Create an AFTER trigger on 

CREATE TRIGGER foo
  AFTER UPDATE OF json_data
  ON myTable
  FOR EACH ROW
  WHEN (new.json_data IS DISTINCT FROM old.json_data)
  EXECUTE PROCEDURE myFunction;

Either,


Insert the id, and new.update_time into a new table, or
Update the update_time setting it the current time, and query later for update_time > last_time_ran.