0

On some occasion I'd like before UPDATE to make sure which columns are changed. To make it as generic as possible, I don't want to use schema, table or column names in function. I found some solution here in SO and other places, and particularly liked idea to use hstore from this answer

Downside of hstore, as said widely, is it that this way I lose data types, everything is stringified.

But using it in context of trigger (while having no complex cols like json or hstore), where both NEW and OLD have same set of cols with according datatypes, I could think of just one problem: NULL and empty values will be not distinguishable.

What other problems I may be faced with, when I detect changes in trigger function like this: 

changes := hstore(NEW) - hstore(OLD);

Alternative seems to use jsonb and then write some jsonb_diff function to discover changes. hstore's offered internal subtract-operation seems way more robust, but maybe I have not considered all weak points there.

I'm using Postgres 9.6.

w.k
  • 8,218
  • 4
  • 32
  • 55
  • 1
    NULL and an empty string **are** distinguishable: https://rextester.com/BPZGE67837 –  Jan 17 '19 at 14:36
  • @a_horse_with_no_name Ok, then I am out of weak points at all ;) – w.k Jan 17 '19 at 14:38

0 Answers0