Trigger with IS DISTINCT FROM on rows with json/jsonb without comparison itemizing?

Question

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?

Answer 1

Immediate problem: type json

There are no equality or inequality operators defined for the Postgres data type json. See:

• How to query a json column for empty objects?
• How to remove known elements from a JSON[] array in PostgreSQL?

Hence row comparisons involving a json column are also bound to fail - with notable exceptions, see below.

There are (imperfect!) workarounds, but don't bother and switch to jsonb, which allows these checks.

Fix function & trigger

You also cannot compare OLD and NEW for INSERT operations, where there is no OLD - only for UPDATE or DELETE. I suggest a column default for modified_at, or a separate trigger function and trigger BEFORE INSERT to overwrite any input with now() unconditionally.

And this function & trigger for UPDATE:

CREATE OR REPLACE FUNCTION on_update_modified()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW IS DISTINCT FROM OLD THEN
      NEW.modified_at = now();
      RETURN NEW;
   ELSE
      RETURN NULL;  -- skip empty update, see below
   END IF;
END
$func$;

CREATE TRIGGER mytable_update  -- doesn't work!
BEFORE UPDATE ON tbl_js
FOR EACH ROW EXECUTE PROCEDURE on_update_modified();

db<>fiddle here (Postgres 9.6)

Nothing different in Postgres 13: db<>fiddle here

RETURN NULL; is optional. If the row has not changed at all (and while running the trigger function already anyway) you might as well skip the UPDATE for the row if it does not change it. That save the considerable cost of writing a new row version in vain. If other triggers depend on it, or for other exotic edge cases, you may still want to go through with it. Then RETURN NEW; - unconditionally at the end of the trigger function. RETURN OLD; would have no benefit after we have established both row values are identical.

Row comparison with json column can still work?

Oddly, yes. Row comparison is processed left-to-right. For the expression NEW IS DISTINCT FROM OLD Postgres can return true immediately on finding the first mismatch (before encountering the problem with json). This may or may not happen, depending on the code path. Quoting the manual on row-wise comparison :

Note

Errors related to the number or types of elements might not occur if the comparison is resolved using earlier columns.