Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl_js (
  id int
, j json                                 -- !
, modified_at timestamptz DEFAULT now()  -- !
);


CREATE TABLE tbl_jsb (
  id int
, j jsonb                                -- !
, modified_at timestamptz DEFAULT now()  -- !
);

CREATE TABLE


-- doesn't work with json
SELECT (1, '{}', now())::tbl_js IS DISTINCT FROM (1, '{}', now())::tbl_js;

ERROR:  operator does not exist: json = json
LINE 2: SELECT (1, '{}', now())::tbl_js IS DISTINCT FROM (1, '{}', n...
                                        ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.


-- but works with jsonb!
SELECT (1, '{}', now())::tbl_jsb IS DISTINCT FROM (1, '{}', now())::tbl_jsb;


?column?


f



SELECT 1


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
   END IF;
END
$func$;

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

CREATE TRIGGER mytable_update  --  works
BEFORE UPDATE ON tbl_jsb
FOR EACH ROW EXECUTE PROCEDURE on_update_modified();

CREATE TRIGGER


-- INSERT = orthogonal to problem
INSERT INTO tbl_js VALUES (1, '{}', NULL);

INSERT 0 1


-- notably does not fail - because row comparison finishes at first column (!)
UPDATE tbl_js SET id = 2
RETURNING *;


id
j
modified_at


2
{}
2025-05-14 23:53:53.957073+00



UPDATE 1


-- fails as expected!
UPDATE tbl_js SET j = '[]'
RETURNING *;

ERROR:  could not identify an equality operator for type json
CONTEXT:  PL/pgSQL function on_update_modified() line 3 at IF


-- INSERT = orthogonal to problem
INSERT INTO tbl_jsb VALUES (1, '{}', now());

INSERT 0 1


-- works as expected!
UPDATE tbl_jsb SET j = '[]'
RETURNING *;


id
j
modified_at


1
[]
2025-05-14 23:53:53.962534+00



UPDATE 1