Postgres – detect change in JSONB column value from ‘null’ to some value across rows

Posted on

Question :

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 -> 'a' != 'null' as tableA) 
       - (select update_time as start_time from fun_table where json_data -> '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”?

Answer :

JSONB supports equality checks. So, if you’re using it, you need only

  1. Create an AFTER trigger on

      AFTER UPDATE OF json_data
      ON myTable
      WHEN (new.json_data IS DISTINCT FROM old.json_data)
      EXECUTE PROCEDURE myFunction;
  2. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *