Is it not possible to do a full row comparison in an INSERT/UPDATE trigger when the table has json or jsonb columns? The error I'm getting makes me think I'd need to individually compare each column because the NEW.*/OLD.*
format can't be used to compare json columns.
CREATE OR REPLACE FUNCTION on_insert_update_modified()
RETURNS TRIGGER AS $$
BEGIN
IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
NEW.modified_at = now();
RETURN NEW;
ELSE
RETURN OLD;
END IF;
END;
$$ language 'plpgsql';
CREATE TRIGGER mytable_insert_update
BEFORE INSERT OR UPDATE ON mytable
FOR EACH ROW
EXECUTE PROCEDURE on_insert_update_modified();
When I insert into the table which contains json and jsonb columns, I get the following error:
ERROR: operator does not exist: json = json
LINE 1: SELECT row(NEW.*) IS DISTINCT FROM row(OLD.*)
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY: SELECT row(NEW.*) IS DISTINCT FROM row(OLD.*)
CONTEXT: PL/pgSQL function on_insert_update_modified() line 3 at IF
Is there a better way out of this?
new is distinct from old
should work.